SQL Statement, select just one record? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Statement, select just one record?

Author

Topic

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-07-11 : 10:39:40

I have a table:


ProjectNum  |  MarkNum | ShipDate | Stack | Level | Position

ProjectNum, MarkNum are Text fields.
ShipDate is DATE field.
Stack, Level, Position are Number (integer) fields.

There can be more then one record with the same ProjectNum, MarkNum, and ShipDate. I insert the record with this information first then later in the application the Stack, Level, and Position are set.

What I need to do is get 1 record from this table using a select statment similar to:


"SELECT ??1?? FROM ShippingLoadTable
 WHERE ProjectNumber = '%s'
 AND MarkNumber = '%s'
 AND Stack IS NULL"

I tried the above statment using TOP 1, but this did not work. Could someone instruct me on what to do/ what I am doing wrong!

Mike B

Edited by - MikeB on 07/11/2002 10:42:04

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-11 : 13:15:36

I think I know what you are going for, but I think i need more details to solve it.

Can you give example INSERT statements that would insert the records, and then give a sample of what you want the records to look like once your done with them?

Here's what I "think" you have
Insert 3 records

update the "first record" with stack 0, level 0, position 0
update the "second record" with stack 0, level 1, position 1
update the "third record" with stack 0, level 2, position 3

Something like that?

Michael

<Yoda>Use the Search page you must. Find the answer you will.

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2002-07-11 : 16:10:55

Thank you very much for you reply. I have resolved the issue at hand using the following select statement:


"SELECT Top 1 * FROM ShippingLoadTable
    WHERE ShippingDate = #%s#
    AND ProjectNumber = '%s'
    AND MarkNumber = '%s'
    AND Stack = 0
 ORDER BY ShippingDate DESC"

FYI the insert statement I use is the following


"INSERT INTO ShippingLoadTable ([ShippingDate], [LoadNumber],[ ProjectNumber], [MarkNumber]) VALUES ('%s', %d, '%s', '%s')", .... variables go here

I insert the record and later set its position fields. I do this using the select statement about and then by setting each field value. I would show more code but I am using c++ and ADO as a front end and all the classes are my own wrapper classes for ADO making the functions look foreign to others. ;)

Mike B

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-11 : 16:14:24

Ahh Access dates, I missed the fact that this was an Access Question.

Well glad you got it resolved.

Michael

<Yoda>Use the Search page you must. Find the answer you will.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources