africaoh
asked on
Howto: SELECT rownumber as unique key.
Does anyone know how one would SELECT a Sequential Rownumber/Recordnumber in a select query?
e.g. (1-5)
Row# Field2
1 a
2 b
3 c
4 d
5 e
e.g. (1-5)
Row# Field2
1 a
2 b
3 c
4 d
5 e
SELECT yourtable.*, *
FROM yourtable
ORDER BY yourtable.row;
<>< kT
FROM yourtable
ORDER BY yourtable.row;
<>< kT
ASKER
phileoca,
Im not sure how your solution will give me an extra column with the rownumber?
Can you maybe explain?
Thank you
Im not sure how your solution will give me an extra column with the rownumber?
Can you maybe explain?
Thank you
ASKER
I just need to select the rownumber in my SELECT statement.
The reason why I need this...
I have two queries that both does a SELECT TOP 5, there is no common key between the two queries, hence I need to create my own somehow, I was hoping I could use rownumbers 1->5.
I need to select * (all 5 rows) from both these two queries, but instead of having 25 rows returned I only need a total of 5 rows (2 columns) in order to do that I need a key between the two.
Does any of the above make sense to anyone?
The reason why I need this...
I have two queries that both does a SELECT TOP 5, there is no common key between the two queries, hence I need to create my own somehow, I was hoping I could use rownumbers 1->5.
I need to select * (all 5 rows) from both these two queries, but instead of having 25 rows returned I only need a total of 5 rows (2 columns) in order to do that I need a key between the two.
Does any of the above make sense to anyone?
Try:
select dcount("Field2","yourtable name","fie ld2<=" & chr(34) & [field2] & chr(34) ) as Sequence, Field2 from yourtablename;
Nic;o)
select dcount("Field2","yourtable
Nic;o)
ASKER
Hi nico,
Your solution works for a simple select on one table but I cant get it working on my query (I use a query to join two existing queries).
Thanks for you help though, appreciate it
Your solution works for a simple select on one table but I cant get it working on my query (I use a query to join two existing queries).
Thanks for you help though, appreciate it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nico,
I got help from another expert via sql-server sp, I figured I have to move my Access queries over to sp anyway might as well get started...
I really appreciate all the help from everyone, Nico Im sure if I tried your final solution earlier we would have gotten that working, hence I will go ahead and accept your answer.
Thanks again everyone
I got help from another expert via sql-server sp, I figured I have to move my Access queries over to sp anyway might as well get started...
I really appreciate all the help from everyone, Nico Im sure if I tried your final solution earlier we would have gotten that working, hence I will go ahead and accept your answer.
Thanks again everyone
ASKER
For those who wanted to know how to do this via sql-server sp: (BulZEye helped me with this)
sorry, extra comma..:
CREATE PROCEDURE dbo.prGetDefectNums
(
@inStartDate DATETIME,
@inEndDate DATETIME
)
AS
BEGIN
SET NOCOUNT ON
SELECT Top 5
IDENTITY(int, 1,1) AS ID_Num
,[tblFinalDefect].[Defects MMO]
,COUNT([tblFinalDefect].[D efectsMMO] ) AS countMMO
into #tblMMODefects
FROM tblFinalOK
INNER JOIN tblFinalDefect
ON [tblFinalOK].[Buildseq]=[t blFinalDef ect].[Buil dseq]
WHERE [tblFinalOK].[FullDate] BETWEEN @inStartDate AND @inEndDate
GROUP BY [tblFinalDefect].[DefectsM MO]
ORDER BY COUNT([tblFinalDefect].[De fectsMMO]) DESC
SELECT Top 5
IDENTITY(int, 1,1) AS ID_Num
,[tblFinalDefect].[Defects EC]
,COUNT([tblFinalDefect].[D efectsEC]) AS countEC
into #tblECDefects
FROM tblFinalOK
INNER JOIN tblFinalDefect
ON [tblFinalOK].[Buildseq]=[t blFinalDef ect].[Buil dseq]
WHERE [tblFinalOK].[FullDate] BETWEEN @inStartDate AND @inEndDate
GROUP BY [tblFinalDefect].[DefectsE C]
ORDER BY COUNT([tblFinalDefect].[De fectsEC]) DESC
SELECT t1.ID_Num
,t1.DefectsMMO AS MMO
,t1.countMMO AS MMOCnt
,t1.DefectsEC AS EC
,t1.countEC AS ECCnt
FROM #tblECDefects t1
INNER JOIN #tblMMODefects t2
ON t1.ID_Num = t2.ID_Num
END
sorry, extra comma..:
CREATE PROCEDURE dbo.prGetDefectNums
(
@inStartDate DATETIME,
@inEndDate DATETIME
)
AS
BEGIN
SET NOCOUNT ON
SELECT Top 5
IDENTITY(int, 1,1) AS ID_Num
,[tblFinalDefect].[Defects
,COUNT([tblFinalDefect].[D
into #tblMMODefects
FROM tblFinalOK
INNER JOIN tblFinalDefect
ON [tblFinalOK].[Buildseq]=[t
WHERE [tblFinalOK].[FullDate] BETWEEN @inStartDate AND @inEndDate
GROUP BY [tblFinalDefect].[DefectsM
ORDER BY COUNT([tblFinalDefect].[De
SELECT Top 5
IDENTITY(int, 1,1) AS ID_Num
,[tblFinalDefect].[Defects
,COUNT([tblFinalDefect].[D
into #tblECDefects
FROM tblFinalOK
INNER JOIN tblFinalDefect
ON [tblFinalOK].[Buildseq]=[t
WHERE [tblFinalOK].[FullDate] BETWEEN @inStartDate AND @inEndDate
GROUP BY [tblFinalDefect].[DefectsE
ORDER BY COUNT([tblFinalDefect].[De
SELECT t1.ID_Num
,t1.DefectsMMO AS MMO
,t1.countMMO AS MMOCnt
,t1.DefectsEC AS EC
,t1.countEC AS ECCnt
FROM #tblECDefects t1
INNER JOIN #tblMMODefects t2
ON t1.ID_Num = t2.ID_Num
END
As far as I know Access does not have any internal numbers for each row.