sort by 2 columns, only get uniques from column 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sort by 2 columns, only get uniques from column 1

Author

Topic

sq2
Starting Member

11 Posts

Posted - 2010-06-21 : 08:22:02

I am trying to get the first image (lowest position) from each gallery (gallery_id)

this gives me a well sorted list, which i am more than happy to iterate through using my server side code:

SELECT *
FROM images
ORDER BY gallery_id, position ASC

however, as i have recently realised, sql is much more powerful than this, and i'm sure that with some use of DISTINCT, GROUP BY or perhaps TOP i can do this in one query...

i think the following is returning the correct rows, however, i need to retrieve all columns within the row data:

SELECT DISTINCT gallery_id
FROM (
SELECT *
FROM images
ORDER BY gallery_id, position ASC
) AS temp
ORDER BY gallery_id ASC

cheers in advance!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-21 : 08:26:40

SELECT t1.*
FROM images as t1 inner join
(
select gallery_id, min(position) as position from images
group by gallery_id
) as t2
on t1.gallery_id=t2.gallery_id and t1.position=t2.position

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-21 : 08:40:03

or maybe like this:

SELECT * 
FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (PARTITION BY gallery_id ORDER BY position ASC))
WHERE RowNumber = 1

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources