RE: Is there an easier way to do this?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: CBretana (CBretana_at_discussions.microsoft.com)
Date: 02/22/05

Next message: Steve Kass: "Re: Get 1st Value in Group"
Previous message: culam: "Re: Insert fail"
In reply to: Brandon Lilly: "RE: Is there an easier way to do this?"
Next in thread: Carl Federl: "RE: Is there an easier way to do this?"
Messages sorted by: [ date ] [ thread ]

Date: Tue, 22 Feb 2005 15:27:04 -0800

All you are doing here is assigning a row number based on the alphabetical
sort of the values of Period and Critical... (i.e., "10" comes before "2")
Is that what you want ?

If you want to sort and number the rows based on the count of records in
each group, then
try running this batch:

Declare @Rows Table
     (RowNum Integer Identity Primary Key Not Null,
      Period TinyInt, Critical Bit, RecCnt Integer)
Insert @Rows(Period,Critical,RecCnt)
Select Period, Critical, Count(*) RecCnt
>From #Data
Group By Period, Critical
Order by RecCnt Desc
-- --------------------------------
Select Period, Critical,
         RecCnt, RowNum
>From @Rows

"Brandon Lilly" wrote:

> Would help if I actually POST my solution:
>
> SELECT DISTINCT
> PERIOD,
> CRITICAL_YN,
> (
> SELECT 1 + COUNT(DISTINCT CAST( B.PERIOD AS char(2) )
> + CAST( B.CRITICAL AS char(1) ) )
> FROM #DataAS B
> WHERE CAST( B.PERIOD AS char(2) ) + CAST( B.CRITICAL AS
> char(1) )
> < CAST( A.PERIOD AS char(2) ) + CAST( A.CRITICAL AS
> char(1) )
> ) AS ORDINAL
> FROM #Data AS A
> ORDER BY 3
>
>
> Brandon
>
>
> "Brandon Lilly" wrote:
>
> > I have a table full of several thousand rows. I want to assign a sequence
> > number based on the number of distinct groups of column values... In this
> > case, Critical & Period, where critical is a bit and period is number from 1
> > to 12. I have a solution, but I was curious if I was making it more
> > complicated than necessary. Order isn't necessary, so results do not have to
> > be the same as mine, but should result in 24 unique groups as shown below:
> >
> > CREATE TABLE #Data (
> > ID int not null,
> > critical bit not null,
> > period tinyint not null,
> > primary key clustered (id, critical, period)
> > )
> >
> > INSERT #Data (ID, critical, period) VALUES (1, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (2, 0, 3)
> > INSERT #Data (ID, critical, period) VALUES (3, 0, 8)
> > INSERT #Data (ID, critical, period) VALUES (4, 0, 1)
> > INSERT #Data (ID, critical, period) VALUES (5, 0, 3)
> > INSERT #Data (ID, critical, period) VALUES (6, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (7, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (8, 0, 6)
> > INSERT #Data (ID, critical, period) VALUES (9, 0, 11)
> > INSERT #Data (ID, critical, period) VALUES (10, 0, 5)
> > INSERT #Data (ID, critical, period) VALUES (11, 1, 9)
> > INSERT #Data (ID, critical, period) VALUES (12, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (13, 0, 1)
> > INSERT #Data (ID, critical, period) VALUES (14, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (15, 0, 10)
> > INSERT #Data (ID, critical, period) VALUES (16, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (17, 0, 4)
> > INSERT #Data (ID, critical, period) VALUES (18, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (19, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (20, 0, 2)
> > INSERT #Data (ID, critical, period) VALUES (21, 1, 2)
> > INSERT #Data (ID, critical, period) VALUES (22, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (23, 0, 7)
> > INSERT #Data (ID, critical, period) VALUES (24, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (25, 0, 4)
> > INSERT #Data (ID, critical, period) VALUES (26, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (27, 0, 4)
> > INSERT #Data (ID, critical, period) VALUES (28, 0, 4)
> > INSERT #Data (ID, critical, period) VALUES (29, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (30, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (31, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (32, 0, 11)
> > INSERT #Data (ID, critical, period) VALUES (33, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (34, 0, 3)
> > INSERT #Data (ID, critical, period) VALUES (35, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (36, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (37, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (38, 0, 1)
> > INSERT #Data (ID, critical, period) VALUES (39, 0, 9)
> > INSERT #Data (ID, critical, period) VALUES (40, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (41, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (42, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (43, 0, 3)
> > INSERT #Data (ID, critical, period) VALUES (44, 0, 3)
> > INSERT #Data (ID, critical, period) VALUES (45, 0, 1)
> > INSERT #Data (ID, critical, period) VALUES (46, 1, 1)
> > INSERT #Data (ID, critical, period) VALUES (47, 0, 5)
> > INSERT #Data (ID, critical, period) VALUES (48, 0, 11)
> > INSERT #Data (ID, critical, period) VALUES (49, 0, 9)
> > INSERT #Data (ID, critical, period) VALUES (50, 0, 8)
> > INSERT #Data (ID, critical, period) VALUES (51, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (52, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (53, 0, 9)
> > INSERT #Data (ID, critical, period) VALUES (54, 0, 10)
> > INSERT #Data (ID, critical, period) VALUES (55, 0, 8)
> > INSERT #Data (ID, critical, period) VALUES (56, 1, 10)
> > INSERT #Data (ID, critical, period) VALUES (57, 0, 10)
> > INSERT #Data (ID, critical, period) VALUES (58, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (59, 0, 3)
> > INSERT #Data (ID, critical, period) VALUES (60, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (61, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (62, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (63, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (64, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (65, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (66, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (67, 0, 11)
> > INSERT #Data (ID, critical, period) VALUES (68, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (69, 0, 9)
> > INSERT #Data (ID, critical, period) VALUES (70, 0, 9)
> > INSERT #Data (ID, critical, period) VALUES (71, 1, 6)
> > INSERT #Data (ID, critical, period) VALUES (72, 0, 3)
> > INSERT #Data (ID, critical, period) VALUES (73, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (74, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (75, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (76, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (77, 0, 1)
> > INSERT #Data (ID, critical, period) VALUES (78, 0, 3)
> > INSERT #Data (ID, critical, period) VALUES (79, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (80, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (81, 1, 3)
> > INSERT #Data (ID, critical, period) VALUES (82, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (83, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (84, 0, 1)
> > INSERT #Data (ID, critical, period) VALUES (85, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (86, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (87, 0, 3)
> > INSERT #Data (ID, critical, period) VALUES (88, 0, 1)
> > INSERT #Data (ID, critical, period) VALUES (89, 0, 2)
> > INSERT #Data (ID, critical, period) VALUES (90, 0, 2)
> > INSERT #Data (ID, critical, period) VALUES (91, 1, 3)
> > INSERT #Data (ID, critical, period) VALUES (92, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (93, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (94, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (95, 0, 9)
> > INSERT #Data (ID, critical, period) VALUES (96, 1, 12)
> > INSERT #Data (ID, critical, period) VALUES (97, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (98, 0, 12)
> > INSERT #Data (ID, critical, period) VALUES (99, 0, 2)
> > INSERT #Data (ID, critical, period) VALUES (100, 0, 3)
> >
> > Expected (or at least acceptable results):
> >
> > PERIOD CRITICAL_YN ORDINAL
> > ------ ----------- -----------
> > 1 0 1
> > 1 1 2
> > 10 0 3
> > 10 1 4
> > 11 0 5
> > 11 1 6
> > 12 0 7
> > 12 1 8
> > 2 0 9
> > 2 1 10
> > 3 0 11
> > 3 1 12
> > 4 0 13
> > 4 1 14
> > 5 0 15
> > 5 1 16
> > 6 0 17
> > 6 1 18
> > 7 0 19
> > 7 1 20
> > 8 0 21
> > 8 1 22
> > 9 0 23
> > 9 1 24
> >
> > (24 row(s) affected)
> >
> >
> >
> > Thanks,
> >
> > Brandon

Next message: Steve Kass: "Re: Get 1st Value in Group"
Previous message: culam: "Re: Insert fail"
In reply to: Brandon Lilly: "RE: Is there an easier way to do this?"
Next in thread: Carl Federl: "RE: Is there an easier way to do this?"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

public folder vs. shared folder
... or sort by category in Explorer. ... except it would be set up in a public folder ... Again, after assigning categories, users could ... Categories option in Outlook is much stronger than in Explorer. ...
(microsoft.public.office.misc)
VBA/Linking Tables
... record so there is no standard number to use. ... I was just wondering if this code is correct for assigning a table ... this information but havn't dealt with this sort of thing in quite some ...
(microsoft.public.access.modulesdaovba)
Re: Newbie Questions: Swithing from Perl to Python
... Todd Stephens wrote: ... > assigning the dictionary elements to a list, ... My personal opinion is that you should be able to do the simplier: ... sort doesn't work like that. ...
(comp.lang.python)
Re: installing to a hdd on a promise pci card without a floppy...
... It sounds like to forgot to sort out the TXTSETUP.OEM stuff? ... Brandon wrote: ... > Created a pnpdrvrs folder in $OEM$ ...
(microsoft.public.win2000.setup_deployment)
Re: Microsoft Security Bulletin MS02-056
... Thanks for the confirmation about the applicability of that number to this ... sort of issue. ... Jerry Bryant wrote: ...
(microsoft.public.sqlserver.security)