How to pick up one type of exam

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)

How to pick up one type of exam

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-05-14 : 20:41:23

Hi,

I have a database table which has the data something like this:

ExamID ExamTypeID
1 1
2 1
3 1
4 2
5 2
6 3
7 3
8 2

Now, I only want to pick one exam of a certain type which means I want to pick up the complete row (the row also includes the text column) of types of exam. Something like this.

ExamID = 1,4 6 should be picked up since their examTypeID is different.

Thanks,
Azam

Mohammad Azam
www.azamsharp.net

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-14 : 22:46:18

I have no idea what you are trying to achieve based on your example. Here is a stab at it though.


DECLARE @table TABLE(ExamID INT, ExamTypeID INT)

INSERT @table(ExamID, ExamTypeID)
	SELECT 1, 1 UNION ALL
	SELECT 2, 1 UNION ALL
	SELECT 3, 1 UNION ALL
	SELECT 4, 2 UNION ALL
	SELECT 5, 2 UNION ALL
	SELECT 6, 3 UNION ALL
	SELECT 7, 3 UNION ALL
	SELECT 8, 2

SELECT ExamID, ExamTypeID
FROM @table

SELECT
	MIN(ExamID),
	ExamTypeID
FROM
	@table
GROUP BY
	ExamTypeID

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-05-14 : 22:49:41

Thanks for the reply! Actually I am trying to select a single Exam of each ExamTypeID.

Mohammad Azam
www.azamsharp.net

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-14 : 23:13:58

Okay. That's what that query does. What are your requirements for finding the single Exam???

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-05-15 : 00:10:38

Hi,

But the code that you posted is static meaning fixed code and if I have other ExamID's then how can I format the code.

Mohammad Azam
www.azamsharp.net

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-15 : 00:48:04

The last select is what does all the work. If the data changes, it doesn't matter. ???

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-05-15 : 11:13:37

Ohh my God! This is soo cool :)

Thanks a gazilion :)

PS:
when I was trying the same query I was not doing the MIN(ExamID) and only doing ExamID hence the query analyzer keeps throwing error messages that ExamID has to belong in the Group by clause!

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources