Selecting of a single row based on criteria

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

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting of a single row based on criteria

Author

Topic

Andy1822
Starting Member

4 Posts

Posted - 2004-08-27 : 14:56:29

I have a table that stores data on the type of fish (Fish_type) and weight (fish_weight) that a group of fisherman (Fisherman_id) catch. Each row of data represents a single catch for the fisherman. A fisherman can have many catches of varying fish_types and Fish_weights. Example:

Fisherman_id Fish_Type Fish_Weight
1 YellowFin Tuna 20
1 Zebra Bass 35
1 YellowFin Tuna 16
2 BlueFish 44
2 Zebra Bass 33

I would like to write a query in Transact SQL (SQLServer 2000) that would return
a single row of data for each fisherman, based on a criteria of:
Take the heaviest YellowFin --if no Yellowfin--> Zebra Bass --if no Zebra Bass----> BlueFish for each fisherman_id. For the data listed above, my result set would be

Fisherman_id Fish_Type Fish_Weight
1 YellowFin Tuna 20
2 Zebra Bass 33

Attempts to create a new field that assigns a numeric value to Fish Type
that can be sorted, and then use the TOP function, did not work.

Any help would be greatly appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-27 : 15:09:28

Use GROUP BY.

Is this homework?

Tara

Andy1822
Starting Member

4 Posts

Posted - 2004-08-27 : 15:23:28

Thanks for the response. As you can tell, I am quite new to SQL. Can you provide the syntax that would give me the results. Assume the table name is tb_Fishing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-27 : 15:31:27

I could. But you need to learn how to do it. If we just give you the answer, then you haven't learned it. Have you looked up GROUP BY in SQL Server Books Online? Does your class material contain information about GROUP BY?

Tara

Andy1822
Starting Member

4 Posts

Posted - 2004-08-27 : 15:40:35

Thanks for responding again Tara. I am familiar with GROUP BY. The main issue is that I want to select a single row for each fisherman based on some decision making tree that cant be solved by sorting or WHERE clauses. I am at a loss. BTW, the question at hand is not related to coursework or an assignment.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-27 : 15:41:33

You would use MAX on the weight column with a GROUP BY. If you are familiar with GROUP BY, then that should be enough information to get you started. Did you look up GROUP BY in SQL Server Books Online for examples?

Tara

Andy1822
Starting Member

4 Posts

Posted - 2004-08-27 : 15:51:50

Thanks, again, Tara. As I have it written below, the resulting query would still return multiple rows per fisherman ID

Select fisherman_id,fish_type,max(fish_weight)
from tb_fisherman
group by fisherman_id,fish_type.

The result would be:
Fisherman_id Fish_Type Fish_Weight
1 YellowFin Tuna 20
1 Zebra Bass 35
2 BlueFish 44
2 Zebra Bass 33

But I only want a single catch for each fisherman, with priority going to Yellowfin over Zebra over BlueFish. If I catch a Yellowfin and a Bluefish, I only want the yellowfin returned.
I may be doing a poor job of explaining it. Thanks for taking the time to respond. I've now looked up GROUP BY in SQL Server Books Online for examples, but havent found something applicable yet.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources