SQL Help - Selecting first, second and so on

[Henrik Larsson]

Hi,

I need help with selecting the following rows from a table looking like this:

ID IP
Query 1 -> 1 2.2.2.2
(ie first IP 1 1.1.1.1 <- Query 2
for each ID) 1 3.3.3.3 (ie second IP for each ID)
-> 2 5.5.5.5
2 6.6.6.6 <-
-> 3 4.4.4.4
3 8.8.8.8 <-
-> 4 7.7.7.7

I can't seem to think of any good way to make the queries, help please!
//Henrik

[HJ]

Hi Henrik,

The first part of your question can be solved by using a query that groups
by the ID field and uses the First value of IP. I have now used the ID field
for the ORDER BY clause, but I imagine you have a better way of identifying
and sorting records.

SELECT tblHenrik.id, First(tblHenrik.ip) AS FirstOfip
FROM tblHenrik
GROUP BY tblHenrik.id
ORDER BY tblHenrik.id;

The second part is harder, especially since you now do not have a way to
sort your records. Do you use any unique record identifier?

HJ

"Henrik Larsson" <si***@home.se> wrote in message
news:9f*************************@posting.google.co m...

Hi,

I need help with selecting the following rows from a table looking like this:
ID IP
Query 1 -> 1 2.2.2.2
(ie first IP 1 1.1.1.1 <- Query 2
for each ID) 1 3.3.3.3 (ie second IP for each ID)
-> 2 5.5.5.5
2 6.6.6.6 <-
-> 3 4.4.4.4
3 8.8.8.8 <-
-> 4 7.7.7.7

I can't seem to think of any good way to make the queries, help please!
//Henrik

[HJ]

I found a way to create the second query too. It is a bit harder than the
first one and maybe somebody else can come up with an easier way.

I created two queries. The first one lists all IPs that are not selected by
the first query. I call this query qryIPsNotInFirst:

SELECT tblHenrik.id, tblHenrik.ip
FROM tblHenrik INNER JOIN qryFirst ON tblHenrik.id = qryFirst.id
WHERE (((tblHenrik.ip)<>[qryFirst].[FirstOfip]));

Then I use this query qryIPsNotInFirst to create a final query called
qrySecond in which I can select the First values of the remaining IPs:

SELECT qryIPsNotInFirst.id, First(qryIPsNotInFirst.ip) AS FirstOfip
FROM qryIPsNotInFirst
GROUP BY qryIPsNotInFirst.id;

That does the trick. Beware of the sorting issue I mentioned in my first
reply.

HJ

"Henrik Larsson" <si***@home.se> wrote in message
news:9f*************************@posting.google.co m...

Hi,

I need help with selecting the following rows from a table looking like this:
ID IP
Query 1 -> 1 2.2.2.2
(ie first IP 1 1.1.1.1 <- Query 2
for each ID) 1 3.3.3.3 (ie second IP for each ID)
-> 2 5.5.5.5
2 6.6.6.6 <-
-> 3 4.4.4.4
3 8.8.8.8 <-
-> 4 7.7.7.7

I can't seem to think of any good way to make the queries, help please!
//Henrik

[Henrik Larsson]

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Henrik Larsson]

Hi,

Thanks for the help, it worked, even though it becomes quite a bit
complicated. I'm going to need to iterate this process 6 times. It's
doable though.

I didn't give you guys the whole picture, the question behind this
problem is that I want to "fan out" a table, instead of having:

ID IP
1 1
1 2
1 3
1 4
1 5

I'd like to have:
ID IP1 IP2 IP3 IP4 IP5
1 1 2 3 4 5

Are there any less complex solutions for this problem? For now I'll use
you suggested solution though.

Thanks for the help so far,
//Henrik

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[James Fortune]

Henrik Larsson <si***@home.se> wrote in message news:<41**********************@news.newsgroups.ws> ...

Hi,

Thanks for the help, it worked, even though it becomes quite a bit
complicated. I'm going to need to iterate this process 6 times. It's
doable though.

I didn't give you guys the whole picture, the question behind this
problem is that I want to "fan out" a table, instead of having:

ID IP
1 1
1 2
1 3
1 4
1 5

I'd like to have:
ID IP1 IP2 IP3 IP4 IP5
1 1 2 3 4 5

Are there any less complex solutions for this problem? For now I'll use
you suggested solution though.

Thanks for the help so far,
//Henrik

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

If you add a primary key called KeyID (AutoNumber) to tblIPs then the
following query (qryOrdinalIPs):

SELECT A.KeyID, A.ID, A.IP, (SELECT COUNT(KeyID) + 1 FROM tblIPs AS B
WHERE B.KeyID < A.KeyID AND B.ID = A.ID) AS theOrdinal FROM tblIPs AS
A GROUP BY A.KeyID, A.ID, A.IP ORDER BY A.ID;

will produce:

KeyID ID IP theOrdinal
1 1 2.2.2.2 1
2 1 1.1.1.1 2
3 1 3.3.3.3 3
4 1 5.5.5.5 4
5 1 4.4.4.4 5
6 2 7.7.7.7 1
7 2 8.8.8.8 2
8 2 9.9.9.9 3

from tblIPs:

KeyID ID IP
1 1 2.2.2.2
2 1 1.1.1.1
3 1 3.3.3.3
4 1 5.5.5.5
5 1 4.4.4.4
6 2 7.7.7.7
7 2 8.8.8.8
8 2 9.9.9.9

You want:
ID IP1 IP2 IP3 IP4 IP5
1 2.2.2.2 1.1.1.1 3.3.3.3 5.5.5.5 4.4.4.4
2 7.7.7.7 8.8.8.8 9.9.9.9

One way is:

SELECT tblIPs.ID, (SELECT qryOrdinalIPs.IP FROM tblIPs AS A INNER JOIN
qryOrdinalIPs ON A.KeyID = qryOrdinalIPs.KeyID WHERE theOrdinal = 1
AND tblIPs.ID = A.ID) AS IP1, (SELECT qryOrdinalIPs.IP FROM tblIPs AS
B INNER JOIN qryOrdinalIPs ON B.KeyID = qryOrdinalIPs.KeyID WHERE
theOrdinal = 2 AND tblIPs.ID = B.ID) AS IP2, (SELECT qryOrdinalIPs.IP
FROM tblIPs AS C INNER JOIN qryOrdinalIPs ON C.KeyID =
qryOrdinalIPs.KeyID WHERE theOrdinal = 3 AND tblIPs.ID = C.ID) AS IP3,
(SELECT qryOrdinalIPs.IP FROM tblIPs AS D INNER JOIN qryOrdinalIPs ON
D.KeyID = qryOrdinalIPs.KeyID WHERE theOrdinal = 4 AND tblIPs.ID =
D.ID) AS IP4, (SELECT qryOrdinalIPs.IP FROM tblIPs AS E INNER JOIN
qryOrdinalIPs ON E.KeyID = qryOrdinalIPs.KeyID WHERE theOrdinal = 5
AND tblIPs.ID = E.ID) AS IP5 FROM tblIPs GROUP BY tblIPs.ID;

James A. Fortune

[Henrik Larsson]

Thanks,

The scrips works like a charm, although it seems to be very heavy on the
CPU. When I run the query access takes 70-80% CPU time for 3-4 minutes
om my 2,8GHz P4 before it's finished with the query and can display it.
I'm running on quite a small data, only a few thousand records. Is this
normal? If not, what could be causing it?

BR
Henrik Larsson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[James Fortune]

Henrik Larsson <si***@home.se> wrote in message news:<41**********************@news.newsgroups.ws> ...

Thanks,

The scrips works like a charm, although it seems to be very heavy on the
CPU. When I run the query access takes 70-80% CPU time for 3-4 minutes
om my 2,8GHz P4 before it's finished with the query and can display it.
I'm running on quite a small data, only a few thousand records. Is this
normal? If not, what could be causing it?

BR
Henrik Larsson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I'll take a look at it. Maybe I'll try VBA also. Perhaps running the
first query as a make-table query and using the resulting table
instead of the query will help. A few thousand records joined a few
times on itself can become a few billion cases that the SQL has to go
through. VBA can probably do everything with one nested variable SQL
statement for each SELECT DISTINCT ID record found.

James A. Fortune

[James Fortune]

Henrik Larsson <si***@home.se> wrote in message news:<41**********************@news.newsgroups.ws> ...

Thanks,

The scrips works like a charm, although it seems to be very heavy on the
CPU. When I run the query access takes 70-80% CPU time for 3-4 minutes
om my 2,8GHz P4 before it's finished with the query and can display it.
I'm running on quite a small data, only a few thousand records. Is this
normal? If not, what could be causing it?

BR
Henrik Larsson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Try this behind a command button on a form:

Dim MyDB As Database
Dim IDRS As Recordset
Dim FanRS As Recordset
Dim NewRS As Recordset
Dim strSQL As String
Dim lngI As Long
Dim lngIDCount As Long
Dim lngJ As Long
Dim lngFanCount As Long
Dim strID As String
Dim tdf As TableDef
Dim fld As Field
Dim boolFanOutFound As Boolean

Set MyDB = CurrentDb
boolFanOutFound = False
For Each tdf In MyDB.TableDefs
If tdf.Name = "tblFanOut" Then
boolFanOutFound = True
Exit For
End If
Next tdf
If Not boolFanOutFound Then
Set tdf = MyDB.CreateTableDef("tblFanOut")
' Create new Field object.
Set fld = tdf.CreateField("ID", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("IP1", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("IP2", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("IP3", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("IP4", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("IP5", dbText, 50)
tdf.Fields.Append fld
tdf.Fields.Refresh
MyDB.TableDefs.Append tdf
MyDB.TableDefs.Refresh
Else
strSQL = "DELETE tblFanOut FROM tblFanOut;"
MyDB.Execute strSQL
End If
strSQL = "SELECT DISTINCT ID FROM tblIPs;"
Set IDRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
strSQL = "tblFanOut"
Set NewRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
If IDRS.RecordCount > 0 Then
IDRS.MoveLast
lngIDCount = IDRS.RecordCount
IDRS.MoveFirst
For lngI = 1 To lngIDCount
strID = IDRS("ID")
strSQL = "SELECT ID, IP FROM tblIPs WHERE ID = " & strID & ";"
Set FanRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If FanRS.RecordCount <> 0 Then
FanRS.MoveLast
lngFanCount = FanRS.RecordCount
If lngFanCount > 5 Then lngFanCount = 5
FanRS.MoveFirst
NewRS.AddNew
NewRS("ID") = FanRS("ID")
For lngJ = 1 To lngFanCount
NewRS("IP" & lngJ) = FanRS("IP")
If lngJ <> lngFanCount Then FanRS.MoveNext
Next lngJ
NewRS.Update
End If
FanRS.Close
Set FanRS = Nothing
If lngI <> lngIDCount Then IDRS.MoveNext
Next lngI
End If
IDRS.Close
Set IDRS = Nothing
Set MyDB = Nothing
MsgBox ("Done.")
James A. Fortune