select query - 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

select query

Author

Topic

chiragvm
Yak Posting Veteran

65 Posts

Posted - 2007-03-19 : 05:02:31

hi to all
i am new in sql2000

i want to create one select query

1 ) select uid from usermaste where status = 0

resul = 3,5,7
2 ) select top 2 smallimage from images where uid = 3
union all
select top 2 smallimage from images where uid = 5
union all
select top 2 smallimage from images where uid = 7

this query is generate 6 row

but here i fire two seperate query and get result.
in this query everytime not fix number of uid
so it deficult to identy fy by programing

the result is possible in one query.insted of two seperatre query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 05:08:33

Try this

SELECT		i.UID,
		MIN(i.SmallImage),
		MAX(i.SmallImage)
FROM		UserMaster AS um
INNER JOIN	Images AS i ON i.UID = um.UID
WHERE		um.Status = 0
GROUP BY	i.UID
ORDER BY	i.UID

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 05:10:01

Variation

SELECT		MIN(i.SmallImage)
FROM		UserMaster AS um
INNER JOIN	Images AS i ON i.UID = um.UID
WHERE		um.Status = 0
GROUP BY	i.UID

UNION ALL

SELECT		MAX(i.SmallImage)
FROM		UserMaster AS um
INNER JOIN	Images AS i ON i.UID = um.UID
WHERE		um.Status = 0
GROUP BY	i.UID

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 06:17:11

If duplicates are not allowed

SELECT		i.UID,
		MIN(i.SmallImage),
		NULLIF(MAX(i.SmallImage), MIN(i.SmallImage))
FROM		UserMaster AS um
INNER JOIN	Images AS i ON i.UID = um.UID
WHERE		um.Status = 0
GROUP BY	i.UID
ORDER BY	i.UID

SELECT		MIN(i.SmallImage)
FROM		UserMaster AS um
INNER JOIN	Images AS i ON i.UID = um.UID
WHERE		um.Status = 0
GROUP BY	i.UID

UNION

SELECT		MAX(i.SmallImage)
FROM		UserMaster AS um
INNER JOIN	Images AS i ON i.UID = um.UID
WHERE		um.Status = 0
GROUP BY	i.UID

Peter Larsson
Helsingborg, Sweden

chiragvm
Yak Posting Veteran

65 Posts

Posted - 2007-03-19 : 07:02:35

thanks for reply but this is not a solution
inorder to smallimage = varchar
so i not use min or max function on that

quote:

Originally posted by Peso

If duplicates are not allowed

SELECT		i.UID,
		MIN(i.SmallImage),
		NULLIF(MAX(i.SmallImage), MIN(i.SmallImage))
FROM		UserMaster AS um
INNER JOIN	Images AS i ON i.UID = um.UID
WHERE		um.Status = 0
GROUP BY	i.UID
ORDER BY	i.UID

SELECT		MIN(i.SmallImage)
FROM		UserMaster AS um
INNER JOIN	Images AS i ON i.UID = um.UID
WHERE		um.Status = 0
GROUP BY	i.UID

UNION

SELECT		MAX(i.SmallImage)
FROM		UserMaster AS um
INNER JOIN	Images AS i ON i.UID = um.UID
WHERE		um.Status = 0
GROUP BY	i.UID

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 07:06:20

Yes, you can use MIN and MAX on VARCHAR!
Why don't you try the suggestion first, befire deciding it will not work?

Do you think people will continue to help you, if you don't even try what we suggest?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 09:45:56

Where did you go?
Did you consider to try my suggestion after all?

Do you have any feed-back?

The reason for using MIN/Max is that the original TOP 2 did not come with an ORDER BY, so they basically just returned two random records. MIN and MAX will also returns two values, though not random.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources