help with TSQL QUERY

[TonyKhela]

Hi,
This should have been simple, but I cant get my head round this query; can someone help me?

table A:
R1 R2 R3
a - 6 - z
d - 2 - p
a - 5 - y
g - 8 - w
d - 6 - w

I need:
R1 R2 R3
a - 6 - z
d - 6 - w
g - 8 - w

I 've allready try this query
Select R1,max(R2),R3 from a group by R1
R1 R2 R3
a - 6 - z
d - 6 - p ***THIS ISN'T CORRECT***
g - 8 - w

The problem with this query:
R1 - ok
R2 - ok
R3 - i'll get the first record the query see so this ain't the corresponding record

Can some one help me out?

Thx,

 

[JamesLean]

So for each distinct value in r1 you want the row with the highest value in r2? This should do it:

SELECT a1.r1, a1.r2, a1.r3
FROM a a1 JOIN (
	SELECT r1, MAX(r2) AS r2
	FROM #a
	GROUP BY r1
) a2 ON a1.r1 = a2.r1 AND a1.r2 = a2.r2
ORDER BY a1.r1

--James

 

[SemperFiDownUnda]

SELECT R1,R2,R3
FROM a as Blah
WHERE R2 = ( SELECT MAX(R2) FROM a WHERE R1 = Blah.R1 )

not that if you have 2 records for with an identicle R1 Value and they both have a R2 that = the MAX for the set of R1 then they both will display.

 

[Ewoud]

Thanks,

But i don't think i explain it that clear..

i have a table with
id - topic - user - date - comment

What i want is a table with only the max date with the corresponding user grouped by the topics
so i can set the last message was post on ... by ...

Hope tis will clear up a bit...

 

[JamesLean]

SELECT topic, user, date
FROM table t1
WHERE date = (
  SELECT MAX(date)
  FROM table
  WHERE topic = t1.topic
)

--James

 

[Ewoud]

thx again but there is still an error.
(date = datum & table = forum)

SELECT topic, user, datum
FROM forum t1
WHERE datum = (
SELECT MAX( datum )
FROM forum
WHERE topic = t1.topic
)

You have an error in your SQL syntax near 'SELECT MAX( datum ) FROM forum WHERE topic = t1.topic ) ' at line 1

 

[JamesLean]

What version of SQL Server are you using? There should be nothing wrong with this statement. --James

 

[SemperFiDownUnda]

user is a keyword and you need to inclose it it []
Besides that JamesLean's SQL statement looks fine.

 

[Ewoud]

i'am using MySQL

 

[Ewoud]

i'am using MySQL 3.23.49
i also tried to inclose with [] but it won't work (same error)

 

[JamesLean]

I've never used MySQL so I can't be sure but I seem to remember reading that it doesn't handle subqueries.

In any case, you should post your question in the correct forum - there is a seperate one for MySQL. This forum is for SQL Server. --James

 

[SemperFiDownUnda]

MySQL does not support subqueries until this latest version.
You can think of MySql as SQL server with 80% of the features chopped off of it.

Ok I feel all the Linux Open Source guys breathing down my neck....think I'll run now.

Seriously people say MySql is great and because it was Free and has support for transaction I would have said YUP...if it wasn't for the fact that MSDE is also free. The fact that it is supported on multiple platforms isn't a huge plus, its more of a geek, "hey this code is cross plaform portable", factor.

 

[SemperFiDownUnda]

Oh and I'm not 100% sure but I thought TSQL was Microsofts® name for its flavor of SQL like PSQL is Oracles. So that combined with the fact this is a Microsoft SQL Server Programming Forum is why you are getting answers that don't work with MySQL.

In future if you can't find another place to ask make sure you give important information, like you are working with a different database server, in your question so you don't have a bunch of people giving you advice that is based on wrong assumtions....and us assuming you where using SQL server is perfectly valid.

 

[Transcend]

You thought right semper

T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL) including transaction control, exception and error handling, row processing, and declared variables. Microsoft's SQL Server and Sybase's SQL server support T-SQL

Transcend