Can you please help me??
-
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
-
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’! ** -
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.....
-
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’! ** -
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! -
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 ShawFor 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 -
@HanShi Thanks its working
but i wanna extra add distinct for C_recid
-
@ChrisM@Work : this are (50200,50194,50430,50191,1) Record Id
-
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...
[font="Courier New"]C_redid column_value
50194 'basic'
50194 'advanced'
50194 'expert'[/font]
...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’! ** -
techmarimuthu (7/2/2013)
@ChrisM@Work : this are (50200,50194,50430,50191,1) Record IdYes 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 ShawFor 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 -
Code To Simulate OP's Table:
CREATE TABLE customer (C_recid INT);INSERT INTO customer VALUES (50200),(50194),(50430),(50191),(1);
Here is my solution:
DECLARE @Lookup TABLE (C_recid INT, OrderSeq SMALLINT);INSERT INTO @Lookup VALUES (50200, 1), (50194, 2), (50430, 3), (50191, 4), (1, 5);
SELECT
c.*
FROM
customer c
INNER JOIN
@Lookup l
ON c.C_recid = l.C_recid
ORDER BY
l.OrderSeq;
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply