Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can you please help me??


Can you please help me??

Author
Message
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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’! **
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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.....
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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’! **
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14959 Visits: 38977
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
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
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 36
@HanShi Thanks its working
but i wanna extra add distinct for C_recid
techmarimuthu
techmarimuthu
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 36
@ChrisM@Work : this are (50200,50194,50430,50191,1) Record Id
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3049 Visits: 3633
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’! **
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search