selecting rows based on value in column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I've been trying to write a query that does the following (I'm failing miserably and haven't found any examples to do this although I suspect it's such a common task there should be heaps...and yes I have zero experience with sql

Say I have a number of columns and that one of these columns have a numerical value

col1 col2 col
1 4
1 3
2 1
2 2
3 8

What I'd like to do is select the rows that have the highest value for col3 respectively. In this example the question should return
{{1, 3, 6}, {2, 3, 4}, {3, 8, 9}
that is, rows 2, 4, and 5

If someone could be so kind as to explain how I could do this, I would really appreciate it
Cheer
/Magnus
 
Peter,

You're still thinking Excel! Tables in Access are not spreadsheets, they
have records and fields, not rows and columns...

Anyway, your selection criteria is not clear. What do you mean by "highest
value for col3 respectively"? I can't see how that matches your example. Can
you clarify?

Nikos

Peter said:
Hi,

I've been trying to write a query that does the following (I'm failing
Click to expand...
miserably and haven't found any examples to do this although I suspect it's
such a common task there should be heaps...and yes I have zero experience
with sql)
Say I have a number of columns and that one of these columns have a numerical value:

col1 col2 col3
1 4 5
1 3 6
2 1 3
2 2 4
3 8 9

What I'd like to do is select the rows that have the highest value for
Click to expand...
col3 respectively. In this example the question should return:
 
col1 col2 col3
1 4 5
1 3 6
2 1 3
2 2 4
3 8 9

What I'd like to do is select the rows that have the highest value for
Click to expand...
col3 respectively. In this example the question should return:
{{1, 3, 6}, {2, 3, 4}, {3, 8, 9}}
that is, rows 2, 4, and 5.
Click to expand...
Hi

You need two queries
1)
SELECT Table1.Col1, Max(Table1.Col3) AS MaxOfCol3
FROM Table1
GROUP BY Table1.Col1;

2)
SELECT Query1.*, Table1.*
FROM Query1 INNER JOIN Table1 ON (Query1.MaxOfCol3 = Table1.Col3) AND
(Query1.Col1 = Table1.Col1);

HTH
Marc
 
Nikos Yannacopoulos said:
You're still thinking Excel! Tables in Access are not spreadsheets, they
have records and fields, not rows and columns...
Click to expand...

Nikos, I think *you* are a bit muddled conceptually. The OP mentioned
SQL and, to quote Joe Celko (he says it often enough!): 'Rows are not
records; fields are not columns; tables are not files; there is no
sequential access or ordering in an RDBMS'.

However, it may be a bit of a stretch to call Jet an RDBMS and from
what I understand of its physical storage, fields and records may be
more in order. But it is definitely columns and rows where SQL is
concerned.

Also, there are no tables in MS Access. You have mistaken Jet to be MS
Access, whereas in fact MS Access objects (forms, reports, etc) are
help in Jet tables!

--
 
Back
Top