sort field in ascending order and get the value of the 2nd row?

  • Thread starter Thread starter dicox
  • Start date Start date
D

dicox

For example if the values are:
1,1,2,3,4 then return 1
1,2,2,3,4 then return 2
1,2,3,4,5 then return 2

Appreciate any help.
 
get the value of the 2nd row
You are showing three rows of data. It looks like you are wanting to return
the second character excluding commas.

Left(Mid([YourField],InStr([YourField],",")+1),InStr(Mid([YourField],InStr([YourField],",")+1),",")-1)
 
in SQL view put:

SELECT TOP (1) fieldname FROM tablename SORT BY fieldname

This can be done in the designer also by opening the query in the designer
by settign the querie's "Top Values" property to 1.

Jacob
 
SELECT TOP 1 T.YourField
FROM (
SELECT TOP 2 YourField
FROM YourTable
ORDER BY YourField) as T
ORDER BY T.YourField DESC

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Thanks for the responses. But now I need to do something a bit more complex.
A need the second highest value for a group.
So if the values are:
Group value
A 2
A 3
A 4
A 5
B 1
B 1
B 3

I need to get as result
A 3 (2nd highest value for group A)
B 1 (2nd highest value - tie with 1 for group B)

Is there a way to do this. Thanks,
 
Query One:

SELECT Group, Value
FROM TheTable as A
WHERE A.Value in (
SELECT Top 2 Value
FROM TheTable as B
WHERE B.Group = A.Group
ORDER BY B.Value Asc)

Query Two uses query one.
SELECT Group, Value
FROM TheTable as C
WHERE C.Value in
(SELECT TOP 1 Value
FROM QueryOne as Q
WHERE Q.Group =C.Group
ORDER BY Q.Value DESC)

You might be able to do that all in one query with the following, but I
would not rely on it being very fast - if it works at all.

SELECT Group, Value
FROM TheTable as C
WHERE C.Value in
(SELECT TOP 1 Value
FROM (
SELECT Group, Value
FROM TheTable as A
WHERE A.Value in (
SELECT Top 2 Value
FROM TheTable as B
WHERE B.Group = A.Group
ORDER BY B.Value Asc)
) as Q
WHERE Q.Group =C.Group
ORDER BY Q.Value DESC)
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Try these two queries --
dicox_1 ---
SELECT dicox.Group, Min(dicox.Value) AS MinOfValue
FROM dicox
GROUP BY dicox.Group;

SELECT Q.Group, Q.Value, (SELECT COUNT(*) FROM dicox Q1 WHERE Q1.[Group] =
Q.[Group] AND Q1.Value < Q.Value)+1 AS Rank
FROM dicox AS Q INNER JOIN dicox_1 ON Q.Group = dicox_1.Group
WHERE (((Q.Value)=[MinOfValue])) OR (((Q.Value)=1) AND (((SELECT COUNT(*)
FROM dicox Q1 WHERE Q1.[Group] = Q.[Group] AND Q1.Value <
Q.Value)+1)=2))
GROUP BY Q.Group, Q.Value
ORDER BY Q.Group, Q.Value;
 
Back
Top