Retrieving rows with 2 indexes

I have a table that contains 2 keys (iId and iVersion) and some other data:

iId int(4) NOT NULL default '0',
iVersion int(4) NOT NULL default '0',
vchName varchar(50) default NULL

The data looks a little like this:

1|1|Fred
1|2|Fred edited once
1|3|Fred edited twice
2|1|Dave
2|2|Dave edited once
2|3|Dave edited twice

I need a sql statement that will return all columns of the latest row (based on the greatest iVersion value) for each unique iId in the table.

So using the data above it should return

1|3|Fred edited twice
2|3|Dave edited twice

Any help would be appreciated!select iId,
iVersion,
vchName
from <table_name>
group by iId
having iVersion = max(iVersion)|||Try:
select iId,
iVersion,
vchName
from <table_name>
where (iId, iVersion) in
( select iId,
max(iVersion) maxVer
from <table_name>
group by iId
);
Or if using Oracle you could use the analytic functions.|||I would suggest using:SELECT *
FROM myTable AS a
WHERE a.iVersion = (SELECT Max(b.iVersion)
FROM myTable AS b
WHERE b.iId = a.iId)-PatP|||May have been that i was using MySQL but none of the above worked!

They did however help me to get some sql that does work...

select iId, vchName, MAX(iVersion) as iVersion
from <Table>
GROUP BY iId

Thanks.|||select iId, vchName, MAX(iVersion) as iVersion
from <Table>
GROUP BY iIdyou may think this works, but it doesn't

even the mysql docs tell you that this gives unpredictable results (holler if you need the link to the page in the docs where it explains this)

what you want is the vchName that comes from the row which has the largest iVersion, but this is not what you are getting, and if it looks like you are getting it, it is a fluke

you could just as easily get this instead --

1|3|Fred edited once
2|3|Dave

i'm sorry if this sounds like i'm dumping all over you, it's not your fault, it's mysql's fault for allowing non-standard sql to run (in any other database system, your query would generate a syntax error)

here's what you want, done without subqueries if you're not on 4.1 yet --
select X.iId
, X.iVersion
, X.vchName
from yourtable as X
inner
join yourtable as Y
on X.iId
= Y.iId
group
by X.iId
, X.iVersion
, X.vchName
having X.iVersion
= max(Y.iVersion)

Retrieving Indexes and SQL Relationships programatically

Hi All,
I have been able to retrieve the tables within a database automatically via
an sql query. Is there a way that I can retrieve the indexes (with the field
names they are indexing) and relationships (Fields plus tables they are
referencing).
What I am trying to achieve is this.
In VB.Net you have datasets. I would like to populate this dataset based on
the tables of the intended database progromatically and setup the
relationships and indexes rather than having to hardcode it in a class
everytime.
I also need to do this to ensure that when an update is sent out and there
have been database design changes that I can compare the structures,indexes
and relationships to what is needed and alter accordingly
Thanks in advance
Mark Hollander
Look at the code of sp_helpindex, sp_fkeys and other related system stored
procedures. You could do this using sp_helptext, as in:
USE master
GO
EXEC sp_helptext sp_helpindex
GO
This will give you an idea of where the required information is stored in
the system tables.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark Hollander" <mark.hollander@.softris.co.za> wrote in message
news:OLDH96d3FHA.128@.tk2msftngp13.phx.gbl...
Hi All,
I have been able to retrieve the tables within a database automatically via
an sql query. Is there a way that I can retrieve the indexes (with the field
names they are indexing) and relationships (Fields plus tables they are
referencing).
What I am trying to achieve is this.
In VB.Net you have datasets. I would like to populate this dataset based on
the tables of the intended database progromatically and setup the
relationships and indexes rather than having to hardcode it in a class
everytime.
I also need to do this to ensure that when an update is sent out and there
have been database design changes that I can compare the structures,indexes
and relationships to what is needed and alter accordingly
Thanks in advance
Mark Hollander