Select 1 top record from each type of record

Question

Select 1 top record from each type of record

rajawat.niranjan

Old Hand

Points: 373
More actions
July 11, 2012 at 10:44 pm

#145393

Hi All,
I have got following data:
ZIP CITY LATITUDE LONGITUDE
D1Dublin53.34410-6.26749
D1Dublin53.34673-6.22762
D1Dublin53.34844-6.25902
D1Dublin53.34854-6.26882
D10Dublin53.33020-6.29876
D11Dublin53.32524-6.25131
D11Dublin53.36834-6.26759
D11Dublin53.38450-6.31176
D11Dublin53.38590-6.29217
D12Dublin53.32689-6.34291
D12Dublin53.32960-6.33472
D12Dublin53.33205-6.29344
D12Dublin53.33302-6.31857
D13Dublin53.32130-6.26704
D13Dublin53.36324-6.21185
D13Dublin53.36998-6.18986
D14Dublin53.31511-6.25458
D14Dublin53.31539-6.23178
D14Dublin53.31855-6.24850
D15Dublin53.35659-6.28591
D15Dublin53.36291-6.33774
D15Dublin53.36414-6.30097
D15Dublin53.36933-6.32962
D15Dublin53.37165-6.33976
D15Dublin53.41073-6.32775
D16Dublin53.30240-6.29219
D16Dublin53.30449-6.23824
D16Dublin53.31327-6.29392
D16Dublin53.33793-6.22439
D17Dublin53.39741-6.23751
D17Dublin53.39875-6.18809
D17Dublin53.40402-6.17819
D17Dublin53.40577-6.19502
Here I want to select top 1 from each zip and city,
hence my results should look like:
D1Dublin53.34410-6.26749
D10Dublin53.33020-6.29876
D11Dublin53.32524-6.25131
D12Dublin53.32689-6.34291
D13Dublin53.32130-6.26704
D14Dublin53.31511-6.25458
D15Dublin53.35659-6.28591
D16Dublin53.30240-6.29219
D17Dublin53.39741-6.23751
for each same zip and city top 1st record should be selected.
I could not figure out how to do this
PLEASE HELP!!!!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

rajawat.niranjan (7/11/2012)
Hi All,
I have got following data:
ZIP CITY LATITUDE LONGITUDE
D1Dublin53.34410-6.26749
D1Dublin53.34673-6.22762
D1Dublin53.34844-6.25902
D1Dublin53.34854-6.26882
D10Dublin53.33020-6.29876
D11Dublin53.32524-6.25131
D11Dublin53.36834-6.26759
D11Dublin53.38450-6.31176
D11Dublin53.38590-6.29217
D12Dublin53.32689-6.34291
D12Dublin53.32960-6.33472
D12Dublin53.33205-6.29344
D12Dublin53.33302-6.31857
D13Dublin53.32130-6.26704
D13Dublin53.36324-6.21185
D13Dublin53.36998-6.18986
D14Dublin53.31511-6.25458
D14Dublin53.31539-6.23178
D14Dublin53.31855-6.24850
D15Dublin53.35659-6.28591
D15Dublin53.36291-6.33774
D15Dublin53.36414-6.30097
D15Dublin53.36933-6.32962
D15Dublin53.37165-6.33976
D15Dublin53.41073-6.32775
D16Dublin53.30240-6.29219
D16Dublin53.30449-6.23824
D16Dublin53.31327-6.29392
D16Dublin53.33793-6.22439
D17Dublin53.39741-6.23751
D17Dublin53.39875-6.18809
D17Dublin53.40402-6.17819
D17Dublin53.40577-6.19502
Here I want to select top 1 from each zip and city,
hence my results should look like:
D1Dublin53.34410-6.26749
D10Dublin53.33020-6.29876
D11Dublin53.32524-6.25131
D12Dublin53.32689-6.34291
D13Dublin53.32130-6.26704
D14Dublin53.31511-6.25458
D15Dublin53.35659-6.28591
D16Dublin53.30240-6.29219
D17Dublin53.39741-6.23751
for each same zip and city top 1st record should be selected.
I could not figure out how to do this
PLEASE HELP!!!!!!

Hi rajawat.niranjan,

It is always better to post the DDL and DML for your table with output data so that anybody can provide you with tested solution.

Anyway I did it for you.Just modify the code according to your need.(you need to change the table name and figures)

Create Table #myzip (ZIP varchar(10) ,CITY varchar(50), LATITUDE Float, LONGITUDE FLOAT)

INSERT INTO #myzip

SELECT 'D1','Dublin',53.34410,-6.26749 UNION ALL

SELECT 'D1','Dublin',53.34673,-6.22762 UNION ALL

SELECT 'D1','Dublin',53.34414,-6.26743 UNION ALL

SELECT 'D10','Dublin',53.34410,-6.26749 UNION ALL

SELECT 'D12','Dublin',53.34410,-6.26749 UNION ALL

SELECT 'D12','Dublin',53.34411,-6.26748 UNION ALL

SELECT 'D12','Dublin',53.34412,-6.26745 UNION ALL

SELECT 'D12','Dublin',53.34413,-6.26746 UNION ALL

SELECT 'D1','Dublin',53.34409,-6.26749 UNION ALL

SELECT 'D13','Dublin',53.34410,-6.26749 UNION ALL

SELECT 'D13','Dublin',53.34411,-6.26746 UNION ALL

SELECT 'D13','Dublin',53.34412,-6.26747 UNION ALL

SELECT 'D13','Dublin',53.34413,-6.26748 UNION ALL

SELECT 'D14','Dublin',53.34410,-6.26749 UNION ALL

SELECT 'D15','Dublin',53.34410,-6.26749 UNION ALL

SELECT 'D15','Dublin',53.34411,-6.26748 UNION ALL

SELECT 'D16','Dublin',53.34410,-6.26749 UNION ALL

SELECT 'D16','Dublin',53.34411,-6.26748 UNION ALL

SELECT 'D17','Dublin',53.34410,-6.26749 UNION ALL

SELECT 'D18','Dublin',53.34410,-6.26749

-- Based on above table and sample data below is your required query

;WITH TAB AS

(SELECT ZIP,CITY,LATITUDE,LONGITUDE,ROW_NUMBER()OVER (PARTITION BY ZIP ORDER BY (SELECT NULL)) AS RN

FROM #myzip)

SELECT * FROM TAB WHERE RN = 1

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

rajawat.niranjan Old Hand Points: 373 More actions · Answer 2

rajawat.niranjan

Old Hand

Points: 373

July 11, 2012 at 11:42 pm

#1511868

That worked

Many Thanks @rhythmk 🙂

rhythmk SSCertifiable Points: 7271 More actions · Answer 3

rajawat.niranjan (7/11/2012)
That worked
Many Thanks @rhythmk 🙂

No issue.:-)

However one question.You did not mention which record you want.So if you want any specific record tweak the query little bit get the name of column for order by clause. Let me know if any issue.

i.e.

[Code="sql"]

;WITH TAB AS

(SELECT ZIP,CITY,LATITUDE,LONGITUDE,ROW_NUMBER()OVER (PARTITION BY ZIP ORDER BY LATITUDE DESC) AS RN

FROM #myzip)

SELECT * FROM TAB WHERE RN = 1

[/code]

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

rajawat.niranjan Old Hand Points: 373 More actions · Answer 4

rhythmk (7/11/2012)
No issue.:-)
However one question.You did not mention which record you want.So if you want any specific record tweak the query little bit get the name of column for order by clause. Let me know if any issue.
i.e.
[Code="sql"]
;WITH TAB AS
(SELECT ZIP,CITY,LATITUDE,LONGITUDE,ROW_NUMBER()OVER (PARTITION BY ZIP ORDER BY LATITUDE DESC) AS RN
FROM #myzip)
SELECT * FROM TAB WHERE RN = 1
[/code]

Thanks @rhythmk for tweak.

I used same, order by LATITUDE.

So it just picks first record having minimum latitude for a particular ZIP.

🙂

rhythmk SSCertifiable Points: 7271 More actions · Answer 5

rajawat.niranjan (7/12/2012)
rhythmk (7/11/2012)
No issue.:-)
However one question.You did not mention which record you want.So if you want any specific record tweak the query little bit get the name of column for order by clause. Let me know if any issue.
i.e.
[Code="sql"]
;WITH TAB AS
(SELECT ZIP,CITY,LATITUDE,LONGITUDE,ROW_NUMBER()OVER (PARTITION BY ZIP ORDER BY LATITUDE DESC) AS RN
FROM #myzip)
SELECT * FROM TAB WHERE RN = 1
[/code]
Thanks @rhythmk for tweak.
I used same, order by LATITUDE.
So it just picks first record having minimum latitude for a particular ZIP.
🙂

I guess it should pick maximum latitude 😉

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

rajawat.niranjan Old Hand Points: 373 More actions · Answer 6

Yeah right 😛

that was mistake putting "DESC" instead of "ASC"

But my intention was "ASC".

😀