How to select specific 2 rows out of a huge table

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

All Forums

General SQL Server Forums

New to SQL Server Programming

How to select specific 2 rows out of a huge table

Author

Topic

ajaypal
Starting Member

6 Posts

Posted - 2013-07-24 : 10:42:11

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

6 Posts

Posted - 2013-07-24 : 10:45:12

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-24 : 11:01:10

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.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-24 : 11:06:15

quote:
Originally posted by MuMu88

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

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

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-24 : 11:13:50

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

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

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

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

ajaypal
Starting Member

6 Posts

Posted - 2013-07-24 : 12:00:29

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

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

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

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-24 : 12:46:42

Here is MySQL command:
[CODE]

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

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources