Top Data - SQL Server Forums

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

Top Data

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 08:06:12

How can I make a filter of the top 3 data of hundreds?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-06 : 08:12:02

Not sure I get you completely. You want the top 3 rows from a table?

If so
SELECT TOP 3 *
FROM SomeTable
ORDER BY SomeColumn

If you don't order, you may get different 3 records on different runs, bcause SQL does not guarentee the order of rows returned without an order by.

If I've misunderstood what you want, please explain in more detail.

--
Gail Shaw

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 08:14:33

In one column I have 20 apples, 17 oranges, 5 grapes. How can I get the top 2 fruits? Meaning, I want applies and oranges as the results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-06 : 08:19:31

quote:
Originally posted by cutiebo2t

In one column I have 20 apples, 17 oranges, 5 grapes. How can I get the top 2 fruits? Meaning, I want applies and oranges as the results.

If you have an ID column then

SELECT DISTINCT TOP 2 Fruits FROM YourTable ORDER BY ID

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 08:29:48

Yes I have ID. But I'm not looking for top 2 fruits. I want to the top 2, oranges and applies be listed all.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-06 : 08:30:41

Some sample data would really help clarify things. The schema of the table would help too.

If you have fruit name and total in seperate columns, then something like

select top 2 fruits from SomeTable order By NumberOfFruits DESC

--
Gail Shaw

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 08:35:46

Fruit Colum
Apples
Apples
Oranges
Oranges
Apples
Grapes

That's my colum and table. I want to filter top 2 fruits which are oranges and apples.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-06 : 08:40:47

That's it? Are there no other columns in the table? (Thought you said you had an ID)
And you want the ones that appear most often?

Try this

SELECT top 2 Fruit FROM
(SELECT count(*) as NoOfAppearences, Fruit from FruitTable Group By Fruit) Sub
Order by NoOfAppearences DESC

--
Gail Shaw

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 08:43:20

Yes with ID. Sorry

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-06 : 08:45:52

Ok. Does my query do what you want?

--
Gail Shaw

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 08:47:42

the formula is not working. It says problem with SELECT.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-06 : 08:54:33

What's the error message?

Did you replace the column names and table names with the real names of the table and its columns?

--
Gail Shaw

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 08:55:51

Yes I did. It says, incorrect syntax near Select

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-06 : 08:59:33

Strange. It syntax checks fine on my local server

--
Gail Shaw

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-06 : 09:01:30

i'm not sure why.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-05-06 : 09:02:15

[code]CREATE TABLE FruitTable (
id INT IDENTITY,
fruit VARCHAR(20)
)
GO

INSERT INTO FruitTable (Fruit)
VALUES ('Apples')
INSERT INTO FruitTable (Fruit)
VALUES ('Apples')
INSERT INTO FruitTable (Fruit)
VALUES ('Oranges')
INSERT INTO FruitTable (Fruit)
VALUES ('Oranges')
INSERT INTO FruitTable (Fruit)
VALUES ('Apples')
INSERT INTO FruitTable (Fruit)
VALUES ('Grapes')
GO

SELECT top 2 Fruit FROM
(SELECT count(*) as NoOfAppearences, Fruit from FruitTable Group By Fruit) Sub
Order by NoOfAppearences DESC[/code]

----------------
Results:

Apples
Oranges

--
Gail Shaw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources