Solved

using ntile for grouping

Posted on 2007-12-04
4
666 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20408232
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
ID: 20408285
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
ID: 20409124
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
ID: 20409176
... how about:

Hope this helps ...

SELECT  Top 1 device_location,service_house_number, service_street
from    DeviceLocationCURRENT
 
--

Open in new window

0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 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