Group By

[robertx]

I want to perform a simple query. I want to group records from a single table by one field and have the records within each group ordered by another field.

I have tried something like this without success:

SELECT Field1, Field2
FROM myTable
GROUP BY Field1
ORDER BY Field2

[saldiboy]

try this...

SELECT Field1, Field2
FROM myTable
GROUP BY Field1, Field2
ORDER BY Field2

[robertx]

That statement returns the same recordset as if I remove the GROUP BY statement.

ie.

SELECT Field1, Field2
FROM myTable
GROUP BY Field1, Field2
ORDER BY Field2

equals

SELECT Field1, Field2
FROM myTable
ORDER BY Field2

[FunkyDexter]

A group by statement must include all the fields in the select list. I'm afraid this is inescapable.

I put an explanation of why in this thread

[szlamany]

GROUP BY collapses "common" rows.

ORDER BY puts the rows into some sorted order.

GROUP BY is rarely used in writing queries. Are you trying to collapse common rows??

ORDER BY is almost always used in writing a query. Otherwise the rows are potentially randomly ordered - which is useless in most cases.

Explain what you want to do in more detail - maybe with some sample data so we can understand it.

[mikee_phil]

why not use distinct rather than a group by, robert x is not using function on a group by field??? question to szlamany??

[szlamany]

DISTINCT, in the past, used a different method of creating the working resultsset. At least with MS SQL Server it did.

That method was to build the working resultset on the server side and then collapse it at the end. This was considered more expensive.

GROUP BY, on the other hand, only inserts a row if it does not already exist - keeping the working resultset on the server side smaller.

With MS SQL Server 2000, MS uses the same GROUP BY logic to perform a DISTINCT.

But I cannot say what other SQL engines do.

So - in our shop - it's a habit to not use DISTINCT - because of the difference in the way the build occurs.

I've also seen sloppy joins - that result in bogus extra rows - being collapsed back down again by DISTINCT. That's a problem being masked by DISTINCT.

It almost sounds like - if you try to read between the lines of the original post - that this person simply wants to ORDER BY both fields:

I want to perform a simple query. I want to group records from a single table by one field and have the records within each group ordered by another field.

I have tried something like this without success:

SELECT Field1, Field2
FROM myTable
GROUP BY Field1
ORDER BY Field2

Maybe all they really want to do is say:

SELECT FIELD1, FIELD2 FROM MYTABLE ORDER BY FIELD1, FIELD2

Do you think that's what they meant?

[mikee_phil]

Maybe all they really want to do is say:

SELECT FIELD1, FIELD2 FROM MYTABLE ORDER BY FIELD1, FIELD2

Do you think that's what they meant?

i think so.. i just want to hear about using group by and distinct methods in detailed from the master... 10nx