Solved

using ntile for grouping

Posted on 2007-12-04
4
629 Views
Last Modified: 2008-02-01
I need to retrieve only one row from the dataset above since it is the same address location except for device_location, service_apt. I don't care which device_location i get., I just need one record.

I tried to get this using NTILE but not luck. I don't want to use any temp tables or any kind of data massaging and I want to get this in one sql statement.

SELECT device_location,service_house_number, service_street
    ,NTILE(1) OVER(PARTITION BY service_house_number, service_street ORDER BY service_house_number ) AS 'Address'
from DeviceLocationCURRENT
 

device_location                service_house_number service_street    service_apt

------------------------------ -------------------- -----------------------------

1543785                        1550                 IRON POINT RD     3324

2390370                        1550                 IRON POINT RD     2821

1545917                        1550                 IRON POINT RD     322

1543769                        1550                 IRON POINT RD     2312

1546597                        1550                 IRON POINT RD     611

1545483                        1550                 IRON POINT RD     2111

2390536                        1550                 IRON POINT RD     2622

1543770                        1550                 IRON POINT RD     2313

1543762                        1550                 IRON POINT RD     2213

1544113                        1550                 IRON POINT RD     1711

1546635                        1550                 IRON POINT RD     1013

1546668                        1550                 IRON POINT RD     412

1546618                        1550                 IRON POINT RD     824

2391609                        1550                 IRON POINT RD     1322

1546677                        1550                 IRON POINT RD     511

1543236                        1550                 IRON POINT RD     111

Open in new window

0
Comment
Question by:LegalZoomer
  • 2
4 Comments
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
SELECT device_location,service_house_number, service_street
    ,row_number() OVER(PARTITION BY service_house_number, service_street ORDER BY service_house_number ) AS Address
from DeviceLocationCURRENT
where Address = 1
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
missed a subselect

SELECT * FROM (
SELECT device_location,service_house_number, service_street
    ,row_number() OVER(PARTITION BY service_house_number, service_street ORDER BY service_house_number ) AS Address
from DeviceLocationCURRENT) X
where Address = 1
0
 
LVL 11

Expert Comment

by:yuching
Comment Utility
Group by also can do the trick.

SELECT service_house_number, service_street,
      MAX(service_apt) AS service_apt, MAX(device_location) AS device_location
FROM DeviceLocationCURRENT
GROUP BY service_house_number, service_street
0
 
LVL 18

Expert Comment

by:Yveau
Comment Utility
... how about:

Hope this helps ...

SELECT  Top 1 device_location,service_house_number, service_street

from    DeviceLocationCURRENT
 

--

Open in new window

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now