Help nEeded!

Hi,
I have a table structure like this

TableName: Common

Columns
PartnerId: int
NativeId: int
FirstName: nvarchar(50)
LastName:nvarchar(50)

1)I should get the records with a minimum native id for a particular PartnerId,
2) if duplicates exists in the above condition i should select top 1 (first record)

How can i do it??

TIA,
sudheerThis is how you get the records corresponding to the minimum IDs:

select Common.*
from Common
inner join
(select PartnerId, min(NativeID) NativeID
from Common) MinIDs
on Common.PartnerId = MinIDs.PartnerId
and Common.NativeID = MinIDs.NativeID

As far as getting the "TOP 1" of the duplicates, you need to specify a sort order, such as by Last Name.|||Originally posted by blindman
This is how you get the records corresponding to the minimum IDs:

select Common.*
from Common
inner join
(select PartnerId, min(NativeID) NativeID
from Common) MinIDs
on Common.PartnerId = MinIDs.PartnerId
and Common.NativeID = MinIDs.NativeID

As far as getting the "TOP 1" of the duplicates, you need to specify a sort order, such as by Last Name.

Hi,
thnx for that but i have other prob with other table

my data is like this

PartnerId NativeId FirstName
2000 45 Sudheer
2000 45 SUdheer1
3000 46 Mytest
3000 46 Mytest1
4000 47 Mytest2
4000 47 Mytest3

... and it goes on

i shuld pick top 1 record of each of the partner ids and my result set shuldbe

PartnerId NativeId FirstName
2000 45 Sudheer
3000 46 Mytest
4000 47 Mytest2

TIA,
sudheer|||Again, "TOP 1" is meaningless without specifying a sort order.

Here is some code that will select the between LastName in alphabetic order when there are duplicate NativeIDs:

select Common.*
from Common
inner join
(select PartnerID,
NativeID,
min(LastName) LastName
from Common
inner join
(select PartnerId, min(NativeID) NativeID
from Common
group by PartnerID) MinIDs
on Common.PartnerId = MinIDs.PartnerId
and Common.NativeID = MinIDs.NativeID
group by PartnerID, NativeID) MinRecords
on Common.PartnerID = MinRecords.PartnerID
and Common.NativeID = MinRecords.NativeID
and Common.LastName = MinRecords.LastName|||Originally posted by blindman
Again, "TOP 1" is meaningless without specifying a sort order. Nah, not quite meaningless. Without a unique ORDER BY, the TOP 1 syntax basically means "pick one random row from this set". I'd say that was practically useless, but not meaningless!

-PatP|||It's meaningless as a phrase when you are trying to pick the "top 1" of each of several groups. The TOP clause cannot even be used for this (efficiently).

I guess I should have specified "top 1" instead of "TOP 1". :)

help me out for storing more data

CREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO

using this script i have created my blog table.

and a procedure given below. i am using to insert data in it.

CREATE PROCEDURE SP_BlogAdd

@.blog_title nvarchar(500),
@.blog_desc_full varchar(8000),
@.blogger_name nvarchar(100),
@.mailid nvarchar(100),
@.blog_desc nvarchar(125),
@.cat_name varchar(100),
@.b_url varchar(250),
@.author nvarchar(250)

AS

INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@.blog_title ,@.blog_desc_full ,@.blogger_name,@.mailid ,@.blog_desc ,@.cat_name ,@.b_url,@.author)
GO

now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).

i want more than this size to store data in it.

please give me some detailed code.

i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.

Please help me out.

regards,
ASIFhttp://www.dbforums.com/showthread.php?t=1605270

help me out for storing more data

CREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO

using this script i have created my blog table.

and a procedure given below. i am using to insert data in it.

CREATE PROCEDURE SP_BlogAdd

@.blog_title nvarchar(500),
@.blog_desc_full varchar(8000),
@.blogger_name nvarchar(100),
@.mailid nvarchar(100),
@.blog_desc nvarchar(125),
@.cat_name varchar(100),
@.b_url varchar(250),
@.author nvarchar(250)

AS

INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@.blog_title ,@.blog_desc_full ,@.blogger_name,@.mailid ,@.blog_desc ,@.cat_name ,@.b_url,@.author)
GO

now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).

i want more than this size to store data in it.

please give me some detailed code.

i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.

Please help me out.

regards,
ASIF
if you are on SQL Server 2005, you can use the VARCHAR(MAX) Type, which will allow you to store up to 2GB of data and use all string and aggregation operations on it, other like the TEXT datatype. Anyway, if you are on SQL Server 200 you have to keep in mind, that you cannot store more than 8096 in one row, therefore you can′t create the table above without having pro′blems while inserting more than 8046 bytes of data. Most people either scaled tables out for this cases in order to split up the data to more than one table referencing them with a 1:1 relation OR, like in your cases used the TEXT type for the columns. You can′t modify the length column, because the length of the TEXT column is 2GB having a pointer stored in the tables with the size of 16Bytes. So anything you put in, can be up to the size of 2GB.