How to write exact query distinct and rownumber comes into role

Question

0.00/5 (No votes)

See more:

I have one scenario in my project. Let me give a simple example of it. Kindly help

table 1

address_id    hub_id           name
100             20           anurag
100             20          abhishek
100             20           rakesh
200             50           nitesh
200             50            neha

table 2

address_id
100
200

now i have to select as below

address_id       hub_id        
100              200

SQL

select distinct(t1.address_id),t1.hub_id from #temp2 t2
join #temp1 t1 on t2.address_id=t1.address_id 
where t1.hub_id=20

now that gives the answer but i have to use it as below

SQL

insert into #temp3(address_id,hub_id,row)
select distinct(t1.address_id),t1.hub_id, ROW_NUMBER() over (order by t1.address_id) as rownumber from #temp2 t2
join #temp1 t1 on t2.address_id=t1.address_id 
where t1.hub_id=20

but this gives me 3 rows

row	address_id	hub_id
1	100	        20
2	100	        20
3	100	        20

but can it be done....inserting it in a separate table using rownumber and fetching only one row like

row   address_id   hub_id
1       100         20

------i got this way ...

SQL

with cte as(select distinct(t1.address_id),t1.hub_id from #temp2 t2
join #temp1 t1 on t2.address_id=t1.address_id
where t1.hub_id=20),
cte1 as
(select address_id, row_number() over (order by address_id) as row from cte)

select * from cte1

But is there any other approach(in single query). Kindly help.

Posted 24-Sep-13 9:15am

anurag19289

Updated 24-Sep-13 9:38am

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 24-Sep-13 17:42pm

Is "row" a columns, a part of the table, or you just use it as a header to present the data sample?
—SA

anurag19289 25-Sep-13 13:56pm

Hi Sergey-

in this query its a part of #temp3

insert into #temp3(address_id,hub_id,row)
select distinct(t1.address_id),t1.hub_id, ROW_NUMBER() over (order by t1.address_id) as rownumber from #temp2 t2
join #temp1 t1 on t2.address_id=t1.address_id
where t1.hub_id=20

--i have created the #temp3 first(which i have not shown)

anurag19289 25-Sep-13 14:00pm

And then i tried searching ..how we can exactly use distinct and then put rownumber to it...i got this cte as a way...

with cte as(select distinct(t1.address_id),t1.hub_id from #temp2 t2
join #temp1 t1 on t2.address_id=t1.address_id
where t1.hub_id=20),
cte1 as
(select address_id, row_number() over (order by address_id) as row from cte)

select * from cte1

--now in this row is a header i am using in cte(select address_id, row_number() over (order by address_id) as row from cte)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-09-24T09:44:00

Solution 1

I don't know why are you trying to join table2 if it stores the same values as table1 (hub_id)?

Have a look here:

SQL

DECLARE @table1 TABLE (address_id INT, hub_id INT, [name] VARCHAR(30))
INSERT INTO @table1 (address_id, hub_id, [name])
SELECT 100, 20, 'anurag'
UNION ALL SELECT 100, 20, 'abhishek'
UNION ALL SELECT 100, 20, 'rakesh'
UNION ALL SELECT 200, 50, 'nitesh'
UNION ALL SELECT 200, 50, 'neha'

SELECT ROW_NUMBER() OVER (ORDER BY address_id) AS RowNo, *
FROM (
    SELECT DISTINCT address_id, hub_id
    FROM @table1
) AS T

Result:

RowNo   address_id      hub_id
1	100		20
2	200		50

Posted 24-Sep-13 9:44am

Maciej Los

Comments

anurag19289 24-Sep-13 16:15pm

Actually Maciej

In my actual query i got stuck in one join.......like the actual query in my project is like

insert into #approve(rownumber,address_id,hub_id,hub_name,geocode_address,distanceByOps,distanceByCtv)
(select ROW_NUMBER() over(order by tg.address_id) as Rownumber,distinct(tg.address_id) as address_id,hub_id,
@hub,distinct(isnull( Landmark,'') +' '+ isnull(Colony,'') +' ' +isnull(Sub_Colony,'')+' '+isnull(CITY,'')) as geocode_address,
distancebyops,distancebyctv
FROM
tbl_Geocode_Address tg with(nolock)
join tbl_user_master tum with(nolock) on tum.address_id=tg.address_id and tum.Facility_ID=@HUB_ID
left join APPROVED_KM_OPSCTV tak on tak.address_id=tg.Address_ID and tak.hub_id=@HUB_ID
WHERE
tg.ADDRESS_ID NOT IN(SELECT TO_ID FROM tbl_approved_distances with(nolock) WHERE FROMID=@HUB_ID) and mmim_key is not null
and tg.Hub_Ids like '%' + cast(@HUB_ID as varchar) +'%'
or tg.ADDRESS_ID IN(SELECT TO_ID FROM tbl_approved_distances with(nolock) WHERE FROMID=@HUB_ID and Distance=0))

--now

join tbl_user_master tum with(nolock) on tum.address_id=tg.address_id and tum.Facility_ID=@HUB_ID

in tbl_user_master (tum)

address_id facility_id
123 20

123 20

123 20

in tbl_geocode_address (tg)
address_id
123

i was joining these two tables....tum.address_id=tg.address_id and tum.Facility_ID=@HUB_ID (@hub_id=20)

--but i was getting 3 duplicate records...i wanted to avoid that...as rownum is also there.,,,it was tough for me ..like how to use distinct

Maciej Los 24-Sep-13 16:19pm

Above data are unique (distinct)! Why? Have a look at name and address. They are differ!

anurag19289 24-Sep-13 16:24pm

name and address columns just i gave for example(like there are many columns in the table)...i have updated now..

Maciej Los 24-Sep-13 16:27pm

What have you updated?

anurag19289 24-Sep-13 16:29pm

i have updated in the comment:

address_id facility_id
123 20

123 20

123 20

in tbl_geocode_address (tg)
address_id
123