Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
8781 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
8781 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

549 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

549 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  
 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.04 seconds. Powered By: Snitz Forums 2000