Solved

Howto: SELECT rownumber as unique key.

Posted on 2004-04-16
9
1,418 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:africaoh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 

Expert Comment

by:LordSparhawk
ID: 10844252
What kind of rownumber do you mean? If your table does not have an 'ID' column or something similar then there is no sequential number to SELECT.

As far as I know Access does not have any internal numbers for each row.
0
 
LVL 11

Expert Comment

by:phileoca
ID: 10844257
SELECT yourtable.*, *
FROM yourtable
ORDER BY yourtable.row;

<>< kT
0
 

Author Comment

by:africaoh
ID: 10844331
phileoca,

Im not sure how your solution will give me an extra column with the rownumber?
Can you maybe explain?

Thank you
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:africaoh
ID: 10844376
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?

0
 
LVL 54

Expert Comment

by:nico5038
ID: 10844756
Try:

select dcount("Field2","yourtablename","field2<=" & chr(34) & [field2] & chr(34) ) as Sequence, Field2 from yourtablename;

Nic;o)
0
 

Author Comment

by:africaoh
ID: 10844943
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
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
ID: 10845041
As long as you have a unique key (and for two tables you can use two fields combined) you can use this.
Even the query could be used like:
select dcount("Field2","yourqueryname","keyfield1 & keyfield2<=" & chr(34) & [keyfield1] & [keyfield2] & chr(34) ) as Sequence, Field2 from yourqueryname;

Getting the idea ?

Nic;o)
0
 

Author Comment

by:africaoh
ID: 10845683
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
0
 

Author Comment

by:africaoh
ID: 10845685
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].[DefectsMMO]
        ,COUNT([tblFinalDefect].[DefectsMMO]) AS countMMO
    into #tblMMODefects
    FROM tblFinalOK
   INNER JOIN tblFinalDefect
      ON [tblFinalOK].[Buildseq]=[tblFinalDefect].[Buildseq]
   WHERE [tblFinalOK].[FullDate] BETWEEN @inStartDate AND @inEndDate
  GROUP BY [tblFinalDefect].[DefectsMMO]
  ORDER BY COUNT([tblFinalDefect].[DefectsMMO]) DESC
 
  SELECT Top 5
         IDENTITY(int, 1,1) AS ID_Num
        ,[tblFinalDefect].[DefectsEC]
        ,COUNT([tblFinalDefect].[DefectsEC]) AS countEC
    into #tblECDefects
    FROM tblFinalOK
   INNER JOIN tblFinalDefect
      ON [tblFinalOK].[Buildseq]=[tblFinalDefect].[Buildseq]
   WHERE [tblFinalOK].[FullDate]  BETWEEN @inStartDate AND @inEndDate
  GROUP BY [tblFinalDefect].[DefectsEC]
  ORDER BY COUNT([tblFinalDefect].[DefectsEC]) 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
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question