SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to select specific 2 rows out of a huge table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ajaypal
Starting Member

Canada
6 Posts

Posted - 07/24/2013 :  10:42:11  Show Profile  Reply with Quote
Hi ,

I have a very large table , and from that table I need just 2 records with column1 = 'A' and column1 = 'B' .
Here I don't think if I can not use OR or IN or Case operators because I need exactly 2 records not more.

Please help me build this query.

ajaypal
Starting Member

Canada
6 Posts

Posted - 07/24/2013 :  10:45:12  Show Profile  Reply with Quote
One more thing I want to make this query efficient because there is no point iterating the whole huge table where maybe 1st and 2nd record hold those values (A and B) . Similarly I want to iterate the whole table unless both the values are found .
Thanks
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/24/2013 :  11:01:10  Show Profile  Visit webfred's Homepage  Reply with Quote
Do you have an index on that column?
Am I right when I assume there are more than one row of each searched value but you need only one of each?

So this should work:
select top (1) * from YourTable where column1 = 'A'
union all
select top (1) * from YourTable where column1 = 'B'



Too old to Rock'n'Roll too young to die.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/24/2013 :  11:06:15  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by MuMu88


SELECT TOP (2) * from YOURTABLE WHERE
     column1 = 'A' and column1 = 'B'
     ORDER BY <SOME CRITERIA>



column1 can't have the value 'A' AND 'B'


Too old to Rock'n'Roll too young to die.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/24/2013 :  11:13:50  Show Profile  Reply with Quote
I realized my mistake as soon as I hit "Post New Reply"
I was hoping you wouldn't notice (you caught it before I was able to delete the post)
quote:
Originally posted by webfred

quote:
Originally posted by MuMu88


SELECT TOP (2) * from YOURTABLE WHERE
     column1 = 'A' and column1 = 'B'
     ORDER BY <SOME CRITERIA>



column1 can't have the value 'A' AND 'B'


Too old to Rock'n'Roll too young to die.


Edited by - MuMu88 on 07/24/2013 11:18:40
Go to Top of Page

ajaypal
Starting Member

Canada
6 Posts

Posted - 07/24/2013 :  12:00:29  Show Profile  Reply with Quote
Hi ,
I am using MySQL so I am using LIMIT instead of TOP . But this keyword won't help me because if My Records are like that : -

Table

Column1
A
A
A
B
A


and I use LIMIT and by using the same query with OR operator , it will give me 2 records with 'A' and no record with 'B'


quote:
Originally posted by MuMu88

I realized my mistake as soon as I hit "Post New Reply"
I was hoping you wouldn't notice (you caught it before I was able to delete the post)
quote:
Originally posted by webfred

quote:
Originally posted by MuMu88


SELECT TOP (2) * from YOURTABLE WHERE
     column1 = 'A' and column1 = 'B'
     ORDER BY <SOME CRITERIA>



column1 can't have the value 'A' AND 'B'


Too old to Rock'n'Roll too young to die.



Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/24/2013 :  12:46:42  Show Profile  Reply with Quote
Here is MySQL command:


(select * from YourTable where column1 = 'A' LIMIT 1)
union
(select * from YourTable where column1 = 'B' LIMIT 1)


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000