Using Group By for some fields and Pagination

[thepixel]

I've setup a query for Pagination, however now I'm seeing that some of these records have duplicate information. I want to only group these records by a few of the columns, but also want other information along with the records. I'm used to MySQL and the Limit 0,50 feature.

So, my query (page 3 of results) looks like:

SELECT * FROM (
    SELECT TOP 50 * FROM (
        SELECT TOP 150 * FROM table
          WHERE Field_Date IS NOT NULL
          ORDER BY Last_Name ASC, First_Name ASC, Middle_Name ASC
    ) AS Selection
      ORDER BY Last_Name DESC, First_Name DESC, Middle_Name DESC
) AS AllOfIt
  ORDER BY Last_Name ASC, First_Name ASC, Middle_Name ASC

Now when I add group by to the mix, I have 2 issues:
1. If I don't group by the exact same fields I'm selecting, i get an error saying the column is invalid.
2. If I only select what I'm grouping by, the query only gives me one page.

I want to group by:
GROUP BY First_Name, Middle_Name, Last_Name

In hopes to narrow down some of these records. Thanks for any help.

 

[gmmastros]

What version of SQL Server are you using? There's some new functionality availble in SQL2005 that addresses this issue.


If you're not sure:

Select @@Version



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[thepixel]

Microsoft SQL Server 2000 - 8.00.760

 

[gmmastros]

Have you tried using the DISTINCT keyword?

SELECT [!]Distinct[/!] TOP 150 * FROM table

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[thepixel]

Distinct isn't working. I'm still getting multiples of the same record in my results.

 

[thepixel]

I ended up resolving my issue by placing each of my non-grouped columns into aggregate functions, example:

SELECT Id, MAX(First_Name) AS First_Name, MAX(Last_Name) AS Last_NAME FROM table WHERE Last_Name IS NOT NULL GROUP BY Id ORDER BY Last_Name ASC, First_Name ASC


and for pagination (page 3 of results)


SELECT Id, MAX(First_Name) AS First_Name, MAX(Last_Name) AS Last_NAME FROM (
SELECT TOP 50 Id, MAX(First_Name) AS First_Name, MAX(Last_Name) AS Last_NAME FROM (
SELECT TOP 150 Id, MAX(First_Name) AS First_Name, MAX(Last_Name) AS Last_NAME FROM jailInmates WHERE Last_Name IS NOT NULL GROUP BY Id ORDER BY Last_Name ASC, First_Name ASC
) AS Selection GROUP BY Id
ORDER BY Last_Name ASC, First_Name DESC
) AS AllOfIt GROUP BY Id ORDER BY Last_Name ASC, First_Name ASC

I used MAX() to try to get whichever row of the group contained a value