SQL Statement To Return First Unique Record

Jan 19, 2007

I've been searching and trying out SQL statements for hours and I'm
still having a problem.

I'm querying 3 tables... and I only want the first unique record
returned.

Currently, the data returned looks like this..............

New York New York A
New York New York B
New York New York C
Los Angeles California A
Los Angeles California B
Los Angeles California C


I want the select statement to return this instead...

New York New York A
Los Angeles California A

I'm using MS SQL server.

please help?


thanks for your help.

View 4 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Return Primary Key From (updated) Record Of Merge Statement

Apr 16, 2014

I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).

I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.

CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL

[code]....

View 4 Replies View Related

How To Return Null Values For Non-exist Record On A Left Join Statement

Oct 16, 2004

I have table Products and Orders that has the following columns:

table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price

The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.

I tried the following select statement:

select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'


Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :

ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null

Obviously, my sql statement won't work becaue the where clause will filter out Product-C.

Could anyone help me figure out how to modify my sql code to get the resultset I want?

Thanks in advance.

View 2 Replies View Related

Attempt To Return Record Set In INSERT...EXEC Statement From ODBC Source(non MSSQL)

Jan 17, 2007

Greeting.

I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.

I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.

Have this problem solution? May be, I must use another method to get my data?



P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.

P.S.S. Sorry for bad english.









View 1 Replies View Related

How To Return First Record Child Record And Count

Jan 31, 2006

I've been looking for examples online to write a SPROC to get some data. Here are the tables.

Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]

I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.

View 3 Replies View Related

SQL Challenge - How To Return A Record Set Starting At A Particular Record?

Feb 25, 2007

I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.

View 4 Replies View Related

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007


I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

How To Return Unique Identifier In SP

Oct 18, 2013

I have trouble to get the uniqueidentifier I just inserted out.

---sp
CREATE PROCEDURE dbo.FAC_Ins_USR
@LAST_NAME AS nvarchar(60)
,@FIRST_NAME AS nvarchar(60)
,@NewID uniqueidentifier output
AS
BEGIN

[Code] ....

The new data went into the table, and the print @myErr shows 0.

But print @myID shows nothing.

---here is the part of the table

CREATE TABLE [dbo].[USERS](
[USER_ID] [uniqueidentifier] DEFAULT NEWID() NOT NULL,...

View 11 Replies View Related

Unique Record

Oct 24, 2005

HI

I HAVE THREE TABLE GEN, PARTY, TXN (GEN & PARRTY IS MASTER TABLE AND
TXN IS TRANSACTION TABLE)
IN TXN TABLE - STORE ALL DATA IN WHICH SAME VOUCHER NUMBER IS REPEATED
ACCORDING TO JOURNAL ENTRY)
NOW PROBLEM IS THIS AT THE TIME OF DISPLAY THE RECORD ON SCREEN I HAVE
TO SHOW ONLY THE UNIQUE VOUCHER NUMBER ONLY..

PLS GUIDE IN THIS QUERY HOW CAN I GET THAT RESULT.


SELECT DISTINCT TXN.VOUCHER, GEN.GLNAME, PARTY.PARTYNAME, TXN.DOCDATE, TXN.GLCODE, TXN.PARTYCODE, TXN.AMOUNT, TXN.VTYPE, TXN.CBCODE, TXN.ADJ, TXN.DC, TXN.NARR
FROM (TXN LEFT JOIN GEN ON GEN.GLCODE=TXN.GLCODE) LEFT JOIN PARTY ON PARTY.PARTYCODE=TXN.PARTYCODE
WHERE TXN.VTYPE='JV'
ORDER BY TXN.VOUCHER


THANKS

ASM

View 3 Replies View Related

Help With A Join That Needs To Return Unique Records

Apr 28, 2008

Hi All,

I need a bit of help with a join. I have 2 tables :

TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue

Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue

TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID


and what I want to retreive is :


For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID

Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

thanks in advance

View 11 Replies View Related

Query To Return Unique Value From Dataset

Feb 13, 2013

I am trying to create a query to return the latest record from a dataset. The code created so far returns multiple records, where I need a single record to be returned.

Please see attached .pdf for full explanation....

View 2 Replies View Related

Return Unique Count (Two Values)

Sep 6, 2013

I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to.

ID | ID_UNIT
1 | 01
1 | 01
1 | 02
1 | 03
1 | 03
1 | 04
1 | 04

I need two results.

Total Units = 7 - easy to do by using count()
Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want.

View 3 Replies View Related

Help With A Join To Return Unique Values

Apr 28, 2008

Hi All,

I need a bit of help with a join. I have 2 tables :

TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue

Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue

TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID


and what I want to retreive is :


For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID

Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID

thanks in advance

View 7 Replies View Related

Unique Record Id Type

Jan 9, 2008

I am trying to make a where clause using the sql server unique record id that is set automatically when a record is inserted. It is the record id that is unique for one table.
In a where clause. What type should go where the _______ is below?
 Here is the program
<%@ page language="vb" %><%@ reference page="try7.aspx" %><% @import namespace="system.data"%><% @import namespace="system.data.sqlclient"%>
<html>   <head>      <title>      </title>      <script language="vb" runat="server">         sub page_load(sender as object,e as eventargs)            response.write(request.queryString("a"))            response.write(",")            response.write(request.queryString("b"))            get1.text=request.queryString("b")         end sub
         sub star(sender as object,e as eventargs)            dim connection1 as new sqlconnection("")
            const q1 as string="update tbltest set code=@code where dxid=@dxid"            dim command1 as sqlcommand=new sqlcommand(q1,connection1)            command1.parameters.add("@code",sqldbtype.varchar).value=get1.text            command1.parameters.add("@dxid",sqldbtype._________).value=request.queryString("a")
            command1.connection.open()            command1.executenonquery()            command1.connection.close()         end sub      </script>   </head>   <body>      <form runat="server">         <asp:textbox id="get1" columns="6" maxlength="6" runat="server" />         <asp:button id="buttonsave" text="click" onclick="star" runat="server" />      </form>   </body></html>

View 3 Replies View Related

How To Get Unique Record With Columns

Mar 20, 2013

I have result set as the attached in screenshot, where the candidateid gets duplicated, but then the column university and careerchoice column has different values for the same candidate. (the CareerChoice and University comes from different tables and i have used union clause)

What want is e.g. CandidateId 186 repeats three times, it should be one row, with three columns all filled with the values which are in different rows.

So that I don't have duplicate candidate ids and i have single row with all column data.

View 6 Replies View Related

Selecting Unique Record

Jul 20, 2005

I have a stored procedure (below), that is supposeto get a Reg Number from a table, (Reg_Number), insuch a way that every time the stored procedure is called,it will get a different reg number, even if the storedprocedure is called simultaneously from two differentplaces,However it is not working that way.If two different users access a function in thereVB program at the same time, the two different userswill get the same reg number.I have looked at the stored procedure, it looks foolproof,yet it is not working that way.Thanks in Advance,Laurence NuttallProgrammer Analyst IIIUCLA - Division of Continuing Education'---------------------------------------------------------------------------Here it is:CREATE PROCEDURE sp_GetNextRegNum@newRegNum char(6) = NULL OUTPUTASLABEL_GET_ANOTHER_REG:Select @newRegNum =(select min(Reg) from reg_number)IF Exists (select Reg from reg_number where reg = @newRegNum )BeginDelete from reg_number where reg = @newRegNumIF @@Error <> 0BeginGoto LABEL_GET_ANOTHER_REGEnd--EndifEndELSEGoTo LABEL_GET_ANOTHER_REG--EndifGO

View 6 Replies View Related

How To Return Primary Unique Index Key On Insert

May 27, 2002

Hi,

I am making a program in Visual Basic .NET with SQL Server 2000.

I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how...

I must do one of them, but don't know how either of them:

-Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created.

or

-get the command in Visual Basic that will send the Insert command with a return field ("Id")

Thanks in advance,
Sebastien Anselmo

View 6 Replies View Related

How To Return An Unique From Two Table With Duplicated Records?

Sep 27, 2005

Hello, everyone:

I have two tables with some duplicated records like,

ZZZTest:
C_IDC1C2C3
10AAA
20BBB
30AAA
40BBB


ZZZTestTable:
D_IDC11C22C33
1AAA
2AAA
3BBB
4BBB
5AAA
6AAA
7BBB


I wand to get the unique records by SELECT / JOIN statement. Now I used a query,

SELECT * FROM ZZZTest t
INNER JOIN ZZZTestTable tt
ON t.Col1=tt.Col11 AND t.Col2=tt.Col22 AND t.Col3 = tt.Col33

and got the records like,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA1AAA
10AAA2AAA
30AAA2AAA
20BBB3BBB
40BBB3BBB
20BBB4BBB
40BBB4BBB
10AAA5AAA
30AAA5AAA
10AAA6AAA
30AAA6AAA
20BBB7BBB
40BBB7BBB



What I am expecting is,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA2AAA
20BBB3BBB
40BBB4BBB

Any suggestion will be great appreciated.

Thanks

ZYT

View 3 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Generate Unique Numbers For Each Record

May 1, 2008

Hi

I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.

Something like table A has



FRUITS
-------------
Oranges
Mangoes
Apricots
....



I want to write a sql query that does something like

SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
fruits
from table A

so the output is


my_number Fruits
080501000001 Oranges
080501000002 Mangoes
080501000003 Apricots
....


regards
Hrishy

View 4 Replies View Related

Join To Show One Row Per Unique Record

Apr 23, 2015

I am new to SQL but trying to do join a few tables to get result showing showing one row per unique record.

Tables include:-

1. REQ
2. RFQ
3. PO
4. DOCUMENT (contains LAST_DOCUMENT_STATUS, DOCUMENT_ID, DOCUMENT_NUMBER, for example, REQ_CANCELLED, REQ_ID, REQ_NO)
5. DOCUMENT_STATUS (contains status of document, REQ_CREATE)
6. DOCUMENT_TRAIL (contains link between documents, PARENT_DOCUMENT, CURRENT_DOCUMENT, for example, REQ_ID (PARENT_DOCUMENT), RFQ_ID (CURRENT_DOCUMENT)
7. PO_REVISION (contains PO REVISION, when link with DOCUMENT, PO_REV_NO)

Currently when i tried to join all the TABLES, i get multiple lines against REQ_NO.

I realised the multiple lines generated due to the following:-

One to many relationships:
A. RFQ - 1 or more PO
B. PO - 1 or more PO_REVISON

I was thinking how to MAX the records in PO to show only the last PO_REVISION. It seems that DOCUMENT_TRAIL will contain 1 base document PO and 1 or more PO_REVISION.

View 13 Replies View Related

How To Combine 2 Records Into 1 Unique Record

Jul 11, 2006

Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.

View 1 Replies View Related

Trying To Identify A Unique Record On Two Separate Tables

Oct 31, 2007

Hello,
I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2
While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index. 
Contents                                   RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description.....
Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database.
Any advice, thoughts or comments would be helpful.
Thanks,P. 
 
 
 
 

View 8 Replies View Related

Table Relationship Question For Unique Record

Apr 9, 2008

Hello Hello,
This Noob has a question.
I have been atempting for days to figure this out.
I have this set up: (See Below)

The Problem I am having is occuring at the Table named OU in Maroon below.
It combines 2 chains of keys into one table to try and make a unique record.
The problem is that it is not.

Pasted below is a Query run on the data within the tables:
As you can see certain data is getting duplicated in this tabel.
I want 1 Unique record in the OU table based upon the Study ID Primary Key and the zCombined Primary Key.

Any Ideas as to what is wrong, or how to make it work would defineatly be appreciated.

Thanks a Bunch


Table: Sponsor
PK: Sponsor ID

Table: Protocol
PK: ProtocolID
FK: Sponsor ID

Table: Study
PK: StudyID
FK: ProtocolID
Table: OUPK: OUIDFK: StudyIDFK: zCombinedID

Table:zCombined
PK: zCombinedID
FK: TempID ~ To Temp Table
FK: ShipTypeID ~ To Ship Type Table
FK: CoordinatorID ~ To Coordinator Table
FK: BoxTypeID ~ To Box Type Table







Sponsor

Protocol

Study

OU~Column1

OU~Column2

Box

Coordinator

Ship Type

Temp


Omni

AAAAA

XPG

S0101

NA

Wheaton

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

Matrix

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

B-1

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

9 x 9 4mL

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

9 x 9 2mL

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

S0201

Wheaton

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

Matrix

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

B-1

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

9 x 9 4mL

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

9 x 9 2mL

Mike Keane

DNA

-40

View 3 Replies View Related

To Get An Unique Sequence Number (record Locking)

Aug 27, 2007

can someone pls show me a way to get an unique sequence at below senario:

PC1 & PC2 using their own local client progam to access to Database Server at SERVER1.
In the SERVER1, there is a table SEQUENCE in a database DATABASE1.
And the table's structure of SEQUENCE are SeqType & SeqNo.
Here is the sample data:

SeqType SeqNo
Invoice 100
DeliveryOrder 200

Now, how to prevent PC1 & PC2 to get a same Invoice No. if they request the Invoice No. at the same time?
Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?

pls advise. thanks.

View 1 Replies View Related

How To Control One Unique Record Per Proc Instance??

Oct 19, 2007

Greetings,

I am running a proc (PROC1) that gets one record at a time and does some work against it.



select min(myid) as NextID
from tbl_ListOfThingsToDo
where AlreadyDone is null

Then once I have the MyID, I set the 'AlreadyDone' bit to True so that no one else picks that record up.


update tbl_listofThingsToDo set AlreadyDone=1 where MyID=(from above)


If there is only one PROC1 running, this is no problem. He just keeps grabbing the next record to operate on.

--but--

If I kick off multiple instances of PROC1 for scaleability reasons, how can I make sure that only one instance gets the next record? For example, couldn't the second instance get the same myid BEFORE the first instance has written that he already got it?

Design is to run 3-5 instances of this to get extra cycles.

Let me know your thoughts, and thanks..


Dan Ribar


View 3 Replies View Related

T-SQL (SS2K8) :: Return A Count Per Unique Check In Invoice Table?

May 12, 2014

We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.

Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103

So I need the statement to update the customer table with the annual payments

Customer Table
Cust# Payments
1 3
2 2
3 2

I get close but just not getting it to sort itself out.

View 9 Replies View Related

SQL Server 2008 :: Return Unique-identifier Of Newly Inserted Row?

Apr 3, 2015

I have the following insert statement:

INSERT INTO [User].User_Profile
(UniqueId, Username, EmailAddress,
Password, BirthDay, BirthMonth,
BirthYear, Age, AccountType, DateCreated,
DeletedDate, DeletedReason, ProfileStatus)
VALUES
(NEWID(), @Username, @EmailAddress,
@Password, @BirthDay, @BirthMonth,
@BirthYeat, @Age, 1, SYSDATETIME(),
null, null, 2)
SELECT @@IDENTITY

As you can I have a uniqueidentifier (UniqueId) column which I populate with NewID() I'm trying to return this as I need it for other functionality of the website but I can't figure out how I can get it after the insert has completed?

View 3 Replies View Related

SQL Server 2012 :: Selecting Unique Record From A Table

Feb 10, 2014

I have a table (Billing Table) having multiple records for people having one record per person per each month.

How to get a list of the guys having record just for one month (say feb) and doesn't have any other months.

View 4 Replies View Related

How To Fetch Record From Temp Table - Unique Combination

Sep 23, 2014

Table :StudentTeacherRelation

Id StdId TeacherName Day subject
1 1 Archana Monday English
2 1 Archana Tue Marathi
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi
6 2 Archana Mon english
7 2 Dipti Tues Hindi

Second table : Student

Id Sname Cid
1 Shalini 1
2 Monika 1
3 Rohan 3

I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come

Id StdId TeacherName Day subject
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi

I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query

select Student.Sname,TeacherName, Day,subject
from StudentTeacherRelation
inner join Student
Student.id = StudentTeacherRelation.StuId
where cid = 1

I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.

show
Id StdId TeacherName Day subject
where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)

So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?

View 1 Replies View Related

Return An Unique Identifier To An ASP.NET Page To Send It As A Parameter Into Another Stored Procedure

May 1, 2007

Hi !
I have a problem with the unique identifier and  don't know how to solve it.
I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.
I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.
Dim cmd As New SqlCommand
cmd.CommandText = "Insertar_Contacto"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
 Thanks!!
 

View 2 Replies View Related

T-SQL (SS2K8) :: Identify Columns Which Will Create Unique Record In A Table

Sep 15, 2014

I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.

View 4 Replies View Related

Return Every Nth Record?

Apr 1, 2008

Is there a SQL command that returns every other result or every 3rd result. The reason being is i am using the data to plot a graph with many thousands of points, it would be useful if not every result was return. I.e. too much data to fit within the screens resolution.

Regards Dan Walmsley

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved