is there a way to do this with query?(giving group numbers)

Question

Post reply

is there a way to do this with query?(giving group numbers)

delta dirac

SSC Veteran

Points: 262
More actions
January 17, 2008 at 11:15 am

#182199

Hi, is it possible in sql to query a table by giving the groups numbers for example i have a two columned table and the data is like this:
c1 c2
------------
a red
a blue
a brown
b white
b gray
c maroon
and all iwant to get with a query is this:
Nu. c1 c2
---------------
1 a red
1 a blue
1 a brown
2 b white
2 b gray
3 c maroon
as you see the original order of the table and the datas of the columns are the same but i only want to give a group number like this; (is it possible with a query or am i dreaming again:))

Viewing 9 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

select dense_rank() over (order by c1, c2) as [Nu.], c1, c2

from dbo.table

order by c1, c2

Edit: Only works in SQL 2005, not in 2000. For that, you need to insert into a temp table/table variable and add the numbers to that. More complex.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

This is a cheap trick

create table #Alpa
(Col1 char(1),
Col2 char(10))
insert into #alpa
Select 'a','red'
Union All
Select 'a','blue'
UNION ALL
Select 'b','white'
Union ALL
Select 'c','Yellow'
Select case col1 when 'a' then 1
when 'b' then 2
when 'c' then 3 end, col1,col2 from #alpa

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

GSquared (1/17/2008)
select dense_rank() over (order by c1, c2) as [Nu.], c1, c2
from dbo.table
order by c1, c2

This gives the output differently from what he wants I think. This is what we get if we run this.

1ablue

2ared

3bwhite

4cYellow

I think he wants 1 if value = 'a'

-Roy

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

drop the C2 in the ORDER by inside DENSE_RANK(), i.e.

SELECT dense_rank() OVER (

ORDER BY c1) AS [Nu], c1, c2

FROM dbo.#Alpa

ORDER BY c1, c2

That will give you ties on C1.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

GSquared SSC Guru Points: 260824 More actions · Answer 5

Matt: You're right. Drop the C2 in the dense_rank function. Meant to have it in the query's Order By, but not in the dense_rank.

(And, actually, now that I look at the original data, don't even want to order by that, since "red" doesn't come before "blue", and can't even do a "desc" order by, since "blue" comes before "brown". Not sure how to preserve the original sequence without some sort of "order by", but the sample doesn't lend itself to one.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 6

Roy: Yeah, a case statement would work, but only with a small table where you have easily defined sets of values, and where the data will never change.

I'm kind of assuming the sample data is just a small sample of something similar to what was being asked about. I always try to design my solutions so they scale to larger, more complex data sets.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

delta dirac SSC Veteran Points: 262 More actions · Answer 7

Mr. Matt Miller and the other anwserer guys thank you very very much i really owned you especially to Matt Miller , i tried the solution and it was a big big table (not small as my example table) and it woked perfectly, my all best wishes to you all.:)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

GSquared (1/17/2008)
Roy: Yeah, a case statement would work, but only with a small table where you have easily defined sets of values, and where the data will never change.

I think You misunderstood me. I was just pointing out that the statement you gave him will not work. I did not have a good solution and I have never used dense_rank() function. I hang around in this Forum so that I can learn from you guys/Ladies.

I am just a beginner DBA. So please take all my comments with a grain of salt.:D

-Roy