Distinct Order By

[johnrowse]

Hi

I know i must be missing something obvious, but i have been trying to get the following to work. Below is my ideal, but i understand it doesn't work like this. If i add dLastVisited to my select distinct statement, no longer will i only return distinct lSamplePointIDs. All I need is the distinct lSamplePointIDs which meet the @SiteID criteria, ordered by dLastVisited (earliest first - no date being the highest priority) Can anyone let me know where i am going wrong?

SELECT Distinct tblPumpPointDownload.lSamplePointID
FROM tblPumpPointDownload
inner join 
tblSamplePointDownload ON tblPumpPointDownload.lSamplePointID = tblSamplePointDownload.lSamplePointID
WHERE(tblSamplePointDownload.lSiteID = @lSiteID)
GROUP BY tblPumpPointDownload.dLastVisited ,lSamplePointID,lpumppointid
order by tblPumpPointDownload.dLastVisited

Thanks

John

 

[gmmastros]

I think you are a little confused about distinct and group by. Distinct is easy to understand, but the group by can be confusing (until you get the hang of it).

When thinking about groups, you need to abandon your thoughts regarding programming, and learn to think is sets. Not everyone can successfully do it.

If I understand you correctly, you want to return each lSamplePointId, and the earliest dLastVisited for each lSamplePointId. In this case, the "group" is lSamplePointId, so it is the only column that should appear in the group by clause.

To get the earliest dLastVisited, you can use the aggregate function MIN. Like this...

SELECT   tblPumpPointDownload.lSamplePointID,
         Min(tblPumpPointDownload.dLastVisited) As EarliestLastVisited
FROM     tblPumpPointDownload
         inner join tblSamplePointDownload 
           ON tblPumpPointDownload.lSamplePointID = tblSamplePointDownload.lSamplePointID
WHERE    tblSamplePointDownload.lSiteID = @lSiteID
GROUP BY tblPumpPointDownload.lSamplePointID
order by EarliestLastVisited

Notice a couple things... I removed the distinct part. Whatever columns are listed in the Group By clause are guaranteed to be unique. Since this code only has the 1 column, you will get a result set that only has one row for each lSamplePointId.

Also noticed that I used the MIN function, and then included "As EarliestLastVisited". This part is called an alias for the column and allows you to name it whatever you want. You can order by a column alias, so you'll see "order by EarliestLastVisited".

This group by stuff is very powerful so it's important that you understand it correctly. If there is any part of this code you don't understand, please let me know and I will explain it further.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnrowse]

George

Thank you so much. It works a treat and you explained it brilliantly – you should be a teacher, if not already.

Thanks again.

John