Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can you please help me?? Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 3:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
I have a customer table

select * from customer

and i need some specific recid member details so i can write a query like this

select * from customer where C_recid in (50200,50194,50430,50191,1)

but the Output of this query ascending order by C_recid

but i wanna output based on (50200,50194,50430,50191,1) this kind only

i got output in union all for individual row

is any other method is possible to get output like this??

Thanks in advance



Post #1469368
Posted Tuesday, July 2, 2013 4:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:40 AM
Points: 2,480, Visits: 3,023
Just add an ORDER BY clause:
select *
from customer
where C_recid in (50200,50194,50430,50191,1)
order by C_recid



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1469372
Posted Tuesday, July 2, 2013 4:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
I was tried that but the Output will be comes based on ascending order based but i wanna the output come arranged on in() class based.....
Post #1469373
Posted Tuesday, July 2, 2013 4:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:40 AM
Points: 2,480, Visits: 3,023
I'm sorry, I didn't understand you wanted the order of the resultset be the same as the sequence order entered in the IN clause.
That requirement is not a standrd function and is harder to implement. If the IN() clause is always the same you could build a CASE to determine the ORDER BY sequence:
select *
from customer
where C_recid in (50200,50194,50430,50191,1)
order by
CASE C_recid
WHEN 50200 THEN 1
WHEN 50194 THEN 2
WHEN 50430 THEN 3
WHEN 50191 THEN 4
WHEN 1 THEN 5
ELSE 6
END

Above CASE statement will change the value of "C_redid" to another value and determine the ORDER BY sequence on this new value. The value of "C_redid" is only changed to determine the ORDER BY and is not visible in the SELECT.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1469377
Posted Tuesday, July 2, 2013 5:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:27 AM
Points: 12,962, Visits: 32,499
that C_recId is probably a varchar?
adding a format to the order by might help instead:
select *
from customer
where C_recid in (50200,50194,50430,50191,1)
order by RIGHT('00000' + C_recid ,5) DESC



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1469396
Posted Tuesday, July 2, 2013 5:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
How is this list (50200,50194,50430,50191,1) generated? Could you leverage the method to create a two-column table instead?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1469399
Posted Tuesday, July 2, 2013 5:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
@HanShi Thanks its working
but i wanna extra add distinct for C_recid
Post #1469401
Posted Tuesday, July 2, 2013 5:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 7, 2013 1:56 AM
Points: 15, Visits: 36
@ChrisM@Work : this are (50200,50194,50430,50191,1) Record Id
Post #1469404
Posted Tuesday, July 2, 2013 5:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:40 AM
Points: 2,480, Visits: 3,023
techmarimuthu (7/2/2013)but i wanna extra add distinct for C_recid

Do you mean there are more records in the resultset with the same value for "C_redid"? And you want just 5 unique records in your resultset that displays each value in the IN() clause?

Can you post some DDL (table definition) statements and sample data (INSERT statements).
To get an unique resultset you can add a GROUP BY or use SELECT DISTINCT to the query. But it depends on the other columns in your SELECT statement, if this will give you the desired result. We need to know how to handle the data in the other columns.

Like if the data in your tables is like this sample below...
C_redid column_value
50194 'basic'
50194 'advanced'
50194 'expert'

...and you want only one row in your resultset: wich value of "column_value" you want in your resultset?


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1469414
Posted Tuesday, July 2, 2013 6:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
techmarimuthu (7/2/2013)
@ChrisM@Work : this are (50200,50194,50430,50191,1) Record Id


Yes I can see that! How does the list come into existence? Is it typed in by you or generated by a front-end?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1469426
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse