Row Number In A Query

Feb 10, 2005

Could anyone help me insert in the first column a row number of this query:
(notice that it has a UNION)

SELECTB.DOCDATE,
B.DOCNUMBR,
A.USERDEF2,
(CASE WHEN B.VENDORID = '41221' OR B.VENDORID = '49697' THEN B.TRXDSCRN ELSE A.VENDNAME END) AS PROVEEDOR,
0 AS INT_EXEN,
0 AS IMP_EXEN,
(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) AS INTERNAS,
(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) AS IMPORTACIONES,
(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) AS IVA,
ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS RETENCION,
(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) +
(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) +
(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) +
ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS TOTAL
FROMPM20000 B INNER JOIN PM00200 A ON
B.VENDORID = A.VENDORID
WHEREB.TAXSCHID >= 'PLAN IVA' AND
B.TAXSCHID <= 'PLAN TRANSP.CON' AND
B.VOIDED = 0 AND
B.TAXAMNT <> 0 AND
UNION ALL
SELECT B.DOCDATE,
B.DOCNUMBR,
A.USERDEF2,
(CASE WHEN B.VENDORID = '41221' OR B.VENDORID = '49697' THEN B.TRXDSCRN ELSE A.VENDNAME END) AS PROVEEDOR,
0 AS INT_EXEN,
0 AS IMP_EXEN,
(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) AS INTERNAS,
(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) AS IMPORTACIONES,
(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) AS IVA,
ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS RETENCION,
(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN 0 ELSE (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) END) +
(CASE WHEN (B.VENDORID = '41221' OR B.VENDORID = '49697') THEN (CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.PRCHAMNT * -1) ELSE B.PRCHAMNT END) ELSE 0 END) +
(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (B.TAXAMNT * -1) ELSE B.TAXAMNT END) +
ISNULL(CASE WHEN (B.DOCTYPE = '4' OR B.DOCTYPE = '5') THEN (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256')*-1 ELSE (SELECT C.TAXAMNT FROM PM80700 C WHERE B.VCHRNMBR = C.VCHRNMBR AND ACTINDX = '1256') END ,0) AS TOTAL
FROMPM00200 A INNER JOIN PM30200 B ON
A.VENDORID = B.VENDORID
WHEREB.DOCTYPE <> 6 AND
B.TAXSCHID >= 'PLAN IVA' AND
B.TAXSCHID <= 'PLAN TRANSP.CON' AND
B.VOIDED = 0 AND
B.TAXAMNT <> 0 AND
ORDER BY DOCDATE

View 3 Replies


ADVERTISEMENT

How To Query A Number (street Number)...

Jul 30, 2007

I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.

View 10 Replies View Related

Returning A Row Number In A Query

Oct 24, 2006

how can i get a row number in a query in ms/sql

View 7 Replies View Related

Sql Query Per Column Number

Sep 8, 2006

is there a way to sql query via a spesific column number

'get from table A row 1 column 1' and then 'get from table A row 2 column 3'

and so on

thanx

View 3 Replies View Related

Number Of Entry Query.

Aug 2, 2006

MSSQL2K
SP4

Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.

--drop table #foo
create table #foo
(empID int,
transDate datetime,
transType varchar(10))

insert into #foo values(1, '01/01/06 01:01:01','Insert')
insert into #foo values(1, '01/01/06 01:01:02','Update')
insert into #foo values(1, '01/01/06 01:01:03','Delete')
insert into #foo values(2, '01/01/06 01:01:01','Insert')
insert into #foo values(2, '01/01/06 01:01:02','Update')

select f.empID, Change =
(select count(transDate) from #foo f2
where f2.empID = f.empID
group by empID),
f.transDate, f.transType
from #foo f

Current results:

132006-01-01 01:01:01.000Insert
132006-01-01 01:01:02.000Update
132006-01-01 01:01:03.000Delete
222006-01-01 01:01:01.000Insert
222006-01-01 01:01:02.000Update

Desired results:

112006-01-01 01:01:01.000Insert
122006-01-01 01:01:02.000Update
132006-01-01 01:01:03.000Delete
212006-01-01 01:01:01.000Insert
222006-01-01 01:01:02.000Update

TIA, CFR

View 5 Replies View Related

Query Using The Record Number

May 7, 2004

happy friday...
my table has 200,000 + records and I want to see the particular record which I think it is causing the problem.
How could i query 195,203rd record?
thank you, yanoroo

View 8 Replies View Related

Get Missing Number From Query?

Apr 20, 2015

In database i have field pwd type varchar(3)

Values inside that field are 1, 2, 3, 4, 5, 7, 8, 9

If you take closely you will notice that 6 is missing. With this code i can found the missing one.

[code]SELECT MIN(u.pwd) + 1 AS FirstMissing
FROM users u
WHERE (u.pwd + 1) <> (SELECT TOP 1 u2.pwd
FROM users u2
WHERE u2.pwd > u.pwd)[code]

But what if there is not missing number. How can i get the highest one + 1.

I have 1, 2, 3 assuming there is no missing number my result needs to be 4.

View 2 Replies View Related

How To Get The Second Biggest Number Using Sql Query?

Sep 20, 2007

Hi,


Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable.

For example, I have the following records:

NumColumn
1
2
3

4
10
11

18

If I use "select Max(NumColumn) from aTable", I will get the number: 18.

But I need to use SQL to the second biggest number: 11.

How to do that? Thanks.

View 17 Replies View Related

Getting The Number Pf Rows In A Query Result

Sep 1, 2004

My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:

"In the forums there is X results for the word X
In the articles there is X results...."

And when the user click one of those lines, the list shows the specific results in that section.

My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…

Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)

Great thanks

View 1 Replies View Related

Phone Number Lookup Query!!

Jun 11, 2004

i am trying to write a query for phone number lookup . The query should be able to search numbers which have anything matching ....
like if the person enters 1918767899 or enters 918767899 the query should be able to find both the records. Itried using the LIKE , but it doesn't work the way it is required.

Pls help !!

Regards

View 5 Replies View Related

Breaking Down A Number Column From A Query?

Sep 18, 2007

I’m okay with simple queries but as I’m no expert and have failed to find perhaps the correct wording to describe this method, if at all possible to do, so I have come to ask here.

What I would like to do is take a column from a query and then break down that column into separate results.

So the full query results:
36,18/09/2007 10:00:00,NULL,000102000304,NULL

The column I would like to brake down is (Unique Reference Number):
000102000304

And I would like to break it down to get the last 2 parts (0003 and 04):
0001 | 02 | 0003 | 04

Is this possible to do?
If so where should I be looking or what should I be looking at?

Many Thanks

View 3 Replies View Related

Restrict The Number Of Records From Query

Feb 9, 2004

Hi,

ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query

i want to implement a query system which will give me the first 50 records, next 50 ,... and so on


can ne body help ?

View 4 Replies View Related

Insert Query And Number Of Fields

Apr 18, 2008

I've found out how to to the Insert into my table (col1, col2) Select (col1, col2...) from othertable where regId= @regId in my earlier question but do i have to name every column as i have about 80 in my table. Can't I use an asterisk or something....

View 4 Replies View Related

Adding Row Number To A Query Result

May 22, 2008

Is there any way I can add record number to my query result?

Exp:

Name Add tel
A 1 st 123456
B H ave. 987456


I need:
No Name Add tel
1 A 1 st 123456
2 B H ave. 987456

Thanks

View 9 Replies View Related

Query To Find A Missing Number

Jul 13, 2006

Hello,I need to write a query to find out a set of missing number in a givensequence.Eg : a Column in some table has the following dataCol11234568910Here I need to write a query to find out that number 7 is missing in thegiven sequence.One possible solution is by using any loop. But I am looking out if the samecan be achieved using any query.Thanks in advance.Regards,Mahesh

View 4 Replies View Related

Query To See If An Int Field Starts With A Certain Number

Nov 3, 2006

How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!

View 3 Replies View Related

Help Designing Query For Any Number Of 5 Parameters

Nov 27, 2007

Hello everyone,

I'm having trouble designing a query for an ASP.NET project I'm working on. The main portion of the query is done and works, but I need to modify it to accept 5 parameters. Below is the main query:





Code Block

@LocID as nvarchar(10)

SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE dbo.ProfileNames.Type = 'Gate' AND dbo.ProfileNames.Location = @LocID
The changes I need to make to this are confusing me because I've had to do something like this before. There are 5 DropDownLists on the page the user can select as parameters for searching/filtering the information. The query above is what's being used now, and causes the page to dynamically generate a certain number of colums based on the number of returned columns.

The parameters that are available are: Gate, Location, Offload Zone, Onload Zone and Equipment. I wwant the user to be able to select all or any number of these as search options but I'm not really sure how to design the query to do this. A co-worker suggested doing something like this:





Code Block

@LocID as nvarchar(10),
@Gate as nvarchar(50),
@Location as nvarchar(50),
@OffLZ as nvarchar(50),
@OnLZ as nvarchar(50)

SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE (dbo.ProfileNames.Type = 'Gate') AND (@Gate IS NULL OR dbo.ProfileNames.Name = @Gate)

AND (dbo.ProfileNames.Location = @LocID)


AND (@Location IS NULL OR dbo.AircraftGateInput.Location = @Location)
AND (@OffLZ IS NULL OR dbo.AircraftGateInput.OffLZ = @OffLZ)
AND (@OnLZ IS NULL OR dbo.AircraftGateInput.OnLZ = @OnLZ)
At this point I'm not a 100% sure on how to test this query in the query designer in Management Studio 2005 (this is also a .NET 2.0 project). If anyone can help, I'd really appreciate it, thanks.

View 3 Replies View Related

Getting Order Number Into Query Grouped By Something

Apr 30, 2008

I have a table of transaction that includes student ids and dates. I need to select all records from the table and include a new value that is the sequential transaction numbered for each student with the oldest transaction for each student being numbered one, the next oldest numbered two and so on. So the result should look like student1, 10/1/2000, 1, student1, 10/15/2000, 2, student1, 2/12/2001, 3, student2, 9/1/1999, 1, student2 10/2/2000, 2, student2 , 12/15/2000, 3, student2, 11/4/2001, 4 and so on.

View 8 Replies View Related

Changing The Number Format In SQL Query

Jan 30, 2008

Hi


I have query

SELECT col1, a.Inv_Amount as Amount
FROM SPS_Oustandings a

I can get the result values for example as 12345.67 as result, but i need it in german format with , used as decimal point and . as thousand seperator

i need output as 12.345,67
My DataGrid (ASP.NET) is directly bound to SP which have this query, i want to show amount in above format in grid.

Please Help,
Thanks

View 6 Replies View Related

Returning Number Of Records Found In Query

Jan 24, 2008

I am trying to return the number of records found by the query but keep seeing -1 in label1. This query should return many records.
         sub findcustomers(sender as object,e as eventargs)            dim connection1 as sqlconnection=new sqlconnection(...)            dim q1 as string="select * from tblcustomers where store='65'"            dim command1 as sqlcommand=new sqlcommand(q1,connection1)            dim a as integer            command1.connection.open()            a=command1.executenonquery()            label1.text=a.tostring()            command1.connection.close()         end sub
What am I doing wrong?

View 8 Replies View Related

A SQL String To Start A Query At A Certain Number And Continuing On

Apr 17, 2008

Hello experts,
 Suddenly I find myself in need of a SQL search that ONLY starts at a record. I need to page through the existing records, but start at the users choice.

View 8 Replies View Related

Creating A Sequential Row Number In A Query Resultset --- Is It Possible?

Feb 17, 2005

Hello everyone,

I have a stored procedure that supplies rows for a front-end DataGrid that allows custom paging. The stored procedure must return the requested "page" of rows as identified by a sproc argument. Currently, I'm loading the the query's result set into a temporary table that has an identity column as primary key. I then run a second query against the temp table that uses the identity column value to strip out the requested "page" of rows and return them to the front-end DataGrid.

I'd like to eliminate the temporary table. To do so I would need to create the equivalent of an identity column in the query's sorted results and reference this value in the WHERE clause in order to return only the requested rows.

Does anyone know of a way to generate a sequential number (starting at 1) within a query (and after the rows have been sorted by the ORDER BY)? I don't think this can be done but I put it out for those who may know better.

Thanks for your help. If you know this is impossible, that would be helpful feedback as well.

BlackCatBone

View 3 Replies View Related

Query To Search For Reference Number That May Appear In Two Different Columns?

Mar 20, 2012

How can I run a query that searches for a reference number that may appear in two different columns? I want to pull all records for idenification numbers that can also show up in a secondary identification column.

View 4 Replies View Related

Change Number To A String In Query Table?

Apr 16, 2015

I have a query which runs exactly as I want it to. Let's say it returns this in a table:

IDNumber
123
212
312

I want to change (find and replace?) all the number 12s in the 'Number' column for a string. I want to change the 12s to the word: HelloWorld. I don't want to update the table, merely the table that is being returned in the query.

View 1 Replies View Related

Query DB For Table Sizes/number Of Rows

Dec 10, 2007

Hi

I have a database with 150 tables. I want to make a query ranking those tables by table size and/or number of rows.

Is this possible?

Many thanks
ds9

View 6 Replies View Related

Query With Row Number,Union And Dynamic Orderby.

Dec 19, 2007

Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.

Existing procedure:


ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)

select @Cond=''

if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)

if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow


exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int


select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl


set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;

SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')


RETURN


can anybody help me pls. ITs very urgent.

Thanks in advance.

View 1 Replies View Related

Maximum Number Of Databases Used For Each Query In 2005?

Mar 22, 2006

In MS Sql Server 2000, if you run a stored procedure that query's morethan 8 databases, you get this error gets raised:Maximum number of databases used for each query has been exceeded. Themaximum allowed is 8.In 2005, does this maximum number still exist? if so, is it still 8, orhas the number been increased?Thanks,Mike

View 2 Replies View Related

Serial Number In Query, In MS Access And SQL Server

Jan 16, 2008

im trying to run a query, my first column should be serial number based on results, how can i do that for example

qry = "Select products, sum(prod_price), sum(prod_sellingprice) from sales group by products"

or

qry = "Select products, sellingperson from sales where seldate=#" & date() & "#"

i want first column of both queries as serial number based on results of query.

View 3 Replies View Related

Transact SQL :: How To Return Number Of Records In Query As If TOP Was Not Used

Jul 21, 2015

What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100  and therefore the TSQL would look like

SELECT TOP (100) * FROM Orders Where OrderDate = '2015/07/21'  ORDER by OrderTime Desc

Now I would like to "tell" the client that only 100 of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECT COUNT(*) FROM Orders Where OrderDate = '2015/07/21'  ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's. Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.

View 6 Replies View Related

How To Get Number Of Records A Query Has Returned Using SqlDataSource Control?

May 17, 2007

Hi,
I'm using SqlDataSource control.
Is there a way to know how many records a query has returned?

View 3 Replies View Related

Increasing The Number Of Characters Displayed In Query Analyzer

Feb 26, 2001

Hi,

I am using SQL Server 7.0.

I have a table with one column defined as Varchar(8000).

The table is populated with rows..

However when I do a Select * from the the Table in query analyzer I only see about 200 Characters even though each column has 8000 characters.

How can I increase the number of characters that are diplayed in the Query Analyzer?

Thankx

KP

View 1 Replies View Related

Count Number Of Positive Dollar Values In Query?

Feb 3, 2012

How do I count the number of positive dollar values in a query? I have various negative and positive dollar values and want to count how many positive I have then I will know how many are negative. I cannot put >0 or >0.00 in the where because it still returns everything cuz the programmer set the datatype to be money

View 4 Replies View Related

Column In Query Results To Number Rows Sequentially

Apr 20, 2015

I need a column in my query results that just numbers the rows sequentially (i.e. 1, 2, 3). How can I do that?

View 6 Replies View Related







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