January 17, 2008 at 11:15 am
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:))
January 17, 2008 at 11:23 am
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
January 17, 2008 at 11:29 am
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
January 17, 2008 at 11:32 am
GSquared (1/17/2008)
select dense_rank() over (order by c1, c2) as [Nu.], c1, c2from 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.
1 a blue
2 a red
3 b white
4 c Yellow
I think he wants 1 if value = 'a'
-Roy
January 17, 2008 at 11:38 am
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?
January 17, 2008 at 11:47 am
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
January 17, 2008 at 11:49 am
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
January 17, 2008 at 12:00 pm
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.:)
January 17, 2008 at 1:05 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply