Rank and Pivot? - SQL Server Forums

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

Rank and Pivot?

Author

Topic

dionsyus
Starting Member

6 Posts

Posted - 2010-04-22 : 04:53:53

Hi,

My data table is like this

PostCode CentreID PrefectureCode RoadPointID Time Distance
0010010 HMM 01 38972 1202 1251.
0010011 HMM 01 38955 1202 1251.
0010012 HMM 01 38945 1202 1257.
0010013 HMM 01 38923 1202 1257.
0010014 HMM 01 38897 1202 1257.

I used this query to limit and select the top 3 (least time required):

SELECT
PostCode,
CentrePointID,
PrefectureCode,
RoadPointID,
[Time],
Distance
FROM (SELECT
ROW_NUMBER() OVER ( PARTITION BY PostCode ORDER BY [Time] ASC ) AS 'RowNumber',
PostCode,
CentrePointID,
PrefectureCode,
RoadPointID,
[Time],
Distance
FROM dbo.Import
) dt
WHERE RowNumber <= 3
ORDER BY Postcode

I am trying to get something like :

PostCode assigned_RFD 2nd_RFD 3rd_RFD
0010000 SPK AIJ MOR
0010010 SPK AIJ MOR
0010011 SPK AIJ MOR
0010012 SPK AIJ MOR

How can I use PIVOT or is it easier to use RANK and PIVOT to get what I need?

Many thanks.

Dennis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 04:58:27

how did you get values SPK AIJ MOR? which tables has that details?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dionsyus
Starting Member

6 Posts

Posted - 2010-04-22 : 05:21:58

quote:
Originally posted by visakh16

how did you get values SPK AIJ MOR? which tables has that details?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The values of SPK AIJ MOR etc are in the data table (I've truncated the rest of the data as it is about 100K).

Basically I am trying to find the least time of each CentreID to a PostalCode and then show the assigned_RFD 2nd_RFD 3rd_RFD from least time required to 3rd least time required.

There are a total of 49 CentreID and a total 100K+ PostCode thus the data table would contain about 5.3 mil records.

Thanks.

Dennis

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 05:30:12

It is always a very good idea to show the wanted output in relation to the shown example data.
If you don't do that then it is like quiz for us.

So can you please show
table structure,
example data,
wanted output in relation to sample data
and maybe you can describe what is for example RFD because it is not a column name.

No, you're never too old to Yak'n'Roll if you're too young to die.

marutivsn
Starting Member

1 Post

Posted - 2010-04-22 : 05:37:56

The columns assigned_RFD, 2nd_RFD, 3rd_RFD ... from which table they are from ... u r not mentioned ..

marutivsn

dionsyus
Starting Member

6 Posts

Posted - 2010-04-22 : 05:43:03

Apologies for not being clear.

My Data source is as such:

CREATE TABLE [dbo].[Import](
[PostCode] [nvarchar](50) NULL,
[CentrePointID] [nvarchar](50) NULL,
[PrefectureCode] [nvarchar](50) NULL,
[RoadPointID] [nvarchar](50) NULL,
[Time] [bigint] NULL,
[Distance] [nvarchar](50) NULL
) ON [PRIMARY]

The example data is (truncated data):

PostCode CentreID PrefectureCode RoadPointID Time Distance
0010010 HMM 01 38972 1202 1251.
0010011 HMM 01 38955 1202 1251.
0010012 HMM 01 38945 1202 1257.
0010013 HMM 01 38923 1202 1257.
0010014 HMM 01 38897 1202 1257.

And I am trying to get:

PostCode NearestCentrePointID 2nd_NearestCentrePointID 3rd_NearestCentrePointID
0010000 SPK AIJ MOR
0010010 SPK AIJ MOR
0010011 SPK AIJ MOR
0010012 SPK AIJ MOR

Based on the value of Time column (which is mins it takes to get from CentreID to the PostCode) in the data source.

Hope I am able to explain what I need better.

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 06:18:17

So SPK and AIJ and MOR are CentrePointIds even like HMM?


So example data should be like this?
0010010 HMM 01 38972 1201 1251.
0010010 SPK 01 38955 1202 1251.
0010010 AIJ 01 38945 1203 1257.
0010010 MOR 01 38923 1204 1257.
And output like this?
0010010 HMM SPK AIJ

No, you're never too old to Yak'n'Roll if you're too young to die.

dionsyus
Starting Member

6 Posts

Posted - 2010-04-22 : 06:24:31

quote:
Originally posted by webfred

So SPK and AIJ and MOR are CentrePointIds even like HMM?

So example data should be like this?
0010010 HMM 01 38972 1201 1251.
0010010 SPK 01 38955 1202 1251.
0010010 AIJ 01 38945 1203 1257.
0010010 MOR 01 38923 1204 1257.
And output like this?
0010010 HMM SPK AIJ

No, you're never too old to Yak'n'Roll if you're too young to die.

Yes, that is the data and output required.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 06:36:05

Then maybe this else wait until visakh comes up with a solution


;with my_cte as
(
SELECT
PostCode,
CentrePointID,
PrefectureCode,
RoadPointID,
[Time],
Distance
FROM (SELECT
ROW_NUMBER() OVER ( PARTITION BY PostCode ORDER BY [Time] ASC ) AS RowNum,
PostCode,
CentrePointID,
PrefectureCode,
RoadPointID,
[Time],
Distance
FROM dbo.Import

)
select 
a.PostCode,
a.CentrePointID as RFD_1,
b.CentrePointID as RFD_2,
c.CentrePointID as RFD_2
from my_cte a
join my_cte b on a.PostCode = b.PostCode and b.RowNum=2
join my_cte c on a.Postcode = c.Postcode and c.RowNum=3
where a.RowNum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

dionsyus
Starting Member

6 Posts

Posted - 2010-04-23 : 01:02:19

Webfred,

Thanks for the suggestion. I gotten an error:

Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'select'.

But could not find what is wrong in the select of my_cte.

Any idea?

Thanks.

Dennis

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 01:12:20

My bad. Try this:


;with my_cte as
(
SELECT
PostCode,
CentrePointID,
PrefectureCode,
RoadPointID,
[Time],
Distance,
ROW_NUMBER() OVER ( PARTITION BY PostCode ORDER BY [Time] ASC ) AS RowNum
FROM dbo.Import
)
select 
a.PostCode,
a.CentrePointID as RFD_1,
b.CentrePointID as RFD_2,
c.CentrePointID as RFD_2
from my_cte a
join my_cte b on a.PostCode = b.PostCode and b.RowNum=2
join my_cte c on a.Postcode = c.Postcode and c.RowNum=3
where a.RowNum=1