I am trying to query a table to get records sorted in a certain way. There are two kinds of records - A and B. I want A's to be sorted on "Start Date" and B's sorted on "End Date". And B's should follow A's. So, Lets say the table looks like: Name StartDate EndDate ---------------------------- A1 9/12 9/21 B1 9/12 9/30 A2 9/05 9/25 B2 9/15 9/25 The query should return: Name StartDate EndDate ---------------------------- A2 9/05 9/25 A1 9/12 9/21 B2 9/15 9/25 B1 9/12 9/30 Can anyone help me please! Thanks a lot. Regards, Calvin
Try: create table #t (name varchar(500), startdate datetime, enddate datetime) insert into #t select 'A1', '20030912','20030921' union all select 'B1', '20030912', '20030930' union all select 'A2', '20030905', '20030925' union all select 'B2', '20030915', '20030925' --Required query select * from (select 1 idd,name, startdate, enddate from #t where name like 'A%' union all select 2,name, startdate, enddate from #t where name like 'B%' ) AA order by idd,case left(name,1) when 'A' then startdate when 'B' then enddate end - Vishal [quoted text, click to view] "Calvin Steele" <cool_steele@lycos.com> wrote in message
news:e7afOz7eDHA.3076@tk2msftngp13.phx.gbl...
> I am trying to query a table to get records sorted in a certain way. There
> are two kinds of records - A and B. I want A's to be sorted on "Start
Date"
> and B's sorted on "End Date". And B's should follow A's.
>
> So, Lets say the table looks like:
>
> Name StartDate EndDate
> ----------------------------
> A1 9/12 9/21
> B1 9/12 9/30
> A2 9/05 9/25
> B2 9/15 9/25
>
> The query should return:
>
> Name StartDate EndDate
> ----------------------------
> A2 9/05 9/25
> A1 9/12 9/21
> B2 9/15 9/25
> B1 9/12 9/30
>
> Can anyone help me please!
> Thanks a lot.
>
> Regards,
> Calvin
>
>
Thanks Vishal, that worked. I have one other question: What if I wanted to sort by desc on end date for B? I tried this but it didnt work: *** order by idd, case left(name,1) when 'A' then startdate when 'B' then enddate desc end SQL server is not taking this desc after the enddate. Thanks, Calvin [quoted text, click to view] >-----Original Message-----
>Try:
>
>create table #t (name varchar(500), startdate datetime,
enddate datetime)
>
>insert into #t
>select 'A1', '20030912','20030921' union all select
>'B1', '20030912', '20030930' union all
select
>'A2', '20030905', '20030925' union all
select
>'B2', '20030915', '20030925'
>
>--Required query
>
>select * from
>(select 1 idd,name, startdate, enddate
>from #t where name like 'A%'
>union all
>select 2,name, startdate, enddate
>from #t where name like 'B%' ) AA
>order by idd,case left(name,1) when 'A' then startdate
when 'B' then enddate
>end
>
>
>- Vishal
>
>"Calvin Steele" <cool_steele@lycos.com> wrote in message
>news:e7afOz7eDHA.3076@tk2msftngp13.phx.gbl...
>> I am trying to query a table to get records sorted in a
certain way. There
>> are two kinds of records - A and B. I want A's to be
sorted on "Start
>Date"
>> and B's sorted on "End Date". And B's should follow A's.
>>
>> So, Lets say the table looks like:
>>
>> Name StartDate EndDate
>> ----------------------------
>> A1 9/12 9/21
>> B1 9/12 9/30
>> A2 9/05 9/25
>> B2 9/15 9/25
>>
>> The query should return:
>>
>> Name StartDate EndDate
>> ----------------------------
>> A2 9/05 9/25
>> A1 9/12 9/21
>> B2 9/15 9/25
>> B1 9/12 9/30
>>
>> Can anyone help me please!
>> Thanks a lot.
>>
>> Regards,
>> Calvin
>>
>>
>
>
>.
select * from (select 1 idd,name, startdate, enddate from #t where name like 'A%' union all select 2,name, startdate, enddate from #t where name like 'B%' ) AA order by idd,case left(name,1) when 'A' then startdate end asc, --check this clause case left(name,1) when 'B' then enddate end desc --check this clause - Vishal [quoted text, click to view] "Calvin" <cool_steele@lycos.com> wrote in message
news:49a101c37be3$392cb610$a601280a@phx.gbl...
> Thanks Vishal, that worked.
> I have one other question:
>
> What if I wanted to sort by desc on end date for B?
>
> I tried this but it didnt work:
>
> ***
> order by
> idd,
> case left(name,1)
> when 'A' then startdate
> when 'B' then enddate desc
> end
>
> SQL server is not taking this desc after the enddate.
>
> Thanks,
> Calvin
>
>
>
> >-----Original Message-----
> >Try:
> >
> >create table #t (name varchar(500), startdate datetime,
> enddate datetime)
> >
> >insert into #t
> >select 'A1', '20030912','20030921' union all select
> >'B1', '20030912', '20030930' union all
> select
> >'A2', '20030905', '20030925' union all
> select
> >'B2', '20030915', '20030925'
> >
> >--Required query
> >
> >select * from
> >(select 1 idd,name, startdate, enddate
> >from #t where name like 'A%'
> >union all
> >select 2,name, startdate, enddate
> >from #t where name like 'B%' ) AA
> >order by idd,case left(name,1) when 'A' then startdate
> when 'B' then enddate
> >end
> >
> >
> >- Vishal
> >
> >"Calvin Steele" <cool_steele@lycos.com> wrote in message
> >news:e7afOz7eDHA.3076@tk2msftngp13.phx.gbl...
> >> I am trying to query a table to get records sorted in a
> certain way. There
> >> are two kinds of records - A and B. I want A's to be
> sorted on "Start
> >Date"
> >> and B's sorted on "End Date". And B's should follow A's.
> >>
> >> So, Lets say the table looks like:
> >>
> >> Name StartDate EndDate
> >> ----------------------------
> >> A1 9/12 9/21
> >> B1 9/12 9/30
> >> A2 9/05 9/25
> >> B2 9/15 9/25
> >>
> >> The query should return:
> >>
> >> Name StartDate EndDate
> >> ----------------------------
> >> A2 9/05 9/25
> >> A1 9/12 9/21
> >> B2 9/15 9/25
> >> B1 9/12 9/30
> >>
> >> Can anyone help me please!
> >> Thanks a lot.
> >>
> >> Regards,
> >> Calvin
> >>
> >>
> >
> >
> >.
> >
Don't see what you're looking for? Try a search.
|
