I have a query that pulls records from a table based on whether is matches one or more of several criteria. I'd like to be able to order the results of this query by the number of conditions a particular row met. So a row that met 3 conditions would rank about a row that only met 2. So far I haven't been able to dent this with any attempt so I'm open to any suggestions.
View 5 RepliesI am trying to do a simple SQL query..the result will be sorted alphabetically by default, but i need to specify say, if there is a "Others" it will be placed last in the list. Is there any way to write the query using ORDER BY?
e.g
Australia
China
Others
USA
and i want it to be:
Australia
CHina
USA
OThers
Consider this SQL:SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1','value3')Simple enough, but is there anyway to specify that the result should beordered exactly like the "IN" clause states? So when this recordsetcomes back, I want it like this:my_field------------value2value1value3Possible?Deane
View 5 Replies View RelatedI've been trying to find out why a simple query containing a couple of top-level order bys produces different results when I introduce TOP into the query. I've found nothing so far, other than the results of both queries (TOP and non-TOP) are different again if I add an index.
All the DDL and DML is below, along with the results. The database uses the BIN2 collator.
I guess there's a simple explanation for this...
DROP table employeeCREATE TABLE employee( id INTEGER IDENTITY (1, 1) NOT NULL, givenname NVARCHAR (20), familyname NVARCHAR (20), CONSTRAINT pk_employee_id PRIMARY KEY (id));insert into employee values('John', 'Smith');insert into employee values('John', 'SMITH');insert into employee values('John', 'Smyth');insert into employee values('John', 'SMYTH');goselect top 10 givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgo-- Dropping top produces results in different order!select givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgo-- Creating an index results in different order on both queries!CREATE INDEX idx_employee_names ON employee (familyname, givenname);goselect top 10 givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgoselect givenname, familynamefrom employee order by lower(familyname) asc, lower(givenname) ascgo
This produces the following results:
givenname familyname
-------------------- --------------------
John SMITH
John Smith
John SMYTH
John Smyth
(4 rows affected)
givenname familyname
-------------------- --------------------
John Smith
John SMITH
John Smyth
John SMYTH
(4 rows affected)
givenname familyname
-------------------- --------------------
John Smith
John SMITH
John Smyth
John SMYTH
(4 rows affected)
givenname familyname
-------------------- --------------------
John SMITH
John Smith
John Smyth
John SMYTH
I have a SELECT Statement that I am using that is pulling from two tables. There won't always be results in the second table so I made a LEFT OUTER JOIN. The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value. I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1
FROM employee LEFT OUTER JOIN
request AS request ON employee.emp_id = request.emp_id
WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))
AS dayspending
FROM employee AS employee_1 LEFT OUTER JOIN
request AS request_1 ON employee_1.emp_id = request_1.emp_id
WHERE (employee_1.emp_id = @emp_id)
GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial,
employee_1.emp_lname
I have the following query
SELECT [KPI].*
FROM
OPENQUERY(LINKED_OLAP,'SELECT
HEAD(TAIL(DESCENDANTS
(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,
([Game].[Game Code].&[1] ,
[Code] ...
The last three columns are dynamically generated because they change during time. Next month they will be different.
I like to introduce aliases for them and to have them in the select as 'TWO_MONTHS_AGO','ONE_MONTH_AGO', 'CURRENT_MONTH'
I wonder if exists something like [KPI].(0), [KPI].(1), and etc.. of the OPENQUERY to get the selected columns by their ordering number...
I need to update many rows in some table. I've made such SQL query:
UPDATE [%TableName%]
SET [%FieldName%] = CASE
WHEN ID = 1 THEN '1'
WHEN ID =2 THEN '2'
....
END;
[%FieldName2%] = CASE
WHEN ID = 1 THEN '1'
WHEN ID = 2 THEN '2'
....
END;
WHERE ID IN {1, 2, ...}
Are there some limitations for CASE operator? How many "when - then" conditions can I include in query?
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
Thanks for whatever information you can provide.
Hi all,Is there a way of counting the number of results/rows from an SqlDataSource which uses a select statement like: "SELECT * FROM TABLE1". I need to get this value from the SqlDataSource into the Sub Page_Load. I don't want to bind to any gridviews, repeaters etc. I just want to get the number of rows/results from the SqlDataSource.Is there a way of doing this?Thanks
View 4 Replies View RelatedI would like to add a field to a query that returns an ordinal number indicating which row of the results it is. Anybuddy know how?
eg. 1,2,3,4,5,6,7....
Thanks,
Carl
I would like to get the top 5000 records from a union. The oringinal query is
SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
I thought that the I could do:
SELECT TOP 5000 * FROM
(
SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
}
This gives me a syntax error that I cannot figure out.
Any ideas?
I am doing some SELECT queries on my database through ASP, but for example, I only want to return the 50 most recent entries that match the criteria. Is there any easy way to limit the number of results returned?
View 1 Replies View RelatedIn other SQL programs I have seen the use of Limit to tell it how many rows to return.
Is there an equivalent in MS-SQL that will let me do a quick Select clause and tell it how many rows to return.
In other words if I just wanted to see the first 10 rows what would I add to Select * from tableA
Thanks!
In MSSQL, is there a way to count the number of instances of asubstring within a string, so that I can sort by that?For example:table tst contains one column: tst_dataif tst_data = "the man with the plan"I'd want a function that counts the occurances of "the"count_substring(tst_data,'the') = 2Basically, I'm making a search engine and I'd like to put the mostrelavent hits at the top of the page.
View 2 Replies View RelatedIam using front page to dispalay my results.
At the bottom it shows me 1/10 i.e 1st page of 10 pages.
but what do i do if i want it to be shown as 1-10 out of 100 (if each page contains 10 results).
or it would be really good if i get count of both no. of recors as well as no. of pages.
Hello,
I am working on a project that displays images. Before you can get to the image, you have to select a category that the image may be in. After that, you select the sub categories. I am trying to display a count of the number of records that the subcategories contain. Here is an example:
The user can make a selection from the categories listed below:
Geographic Area
Time Period
Topic
Record Type
If the user selects time period, he/she is taken to a list of subcategories. I would like to display the subcategories with a count of the number of records that will be displayed if it is selected. Listed below is an example of what this would look like:
Colonial (10) -----the number in parenthesis is the number of records that will be displayed if selected------
Gilded Age (12)
Revolutionary (9)
Progressive Era (22)
Is there a way to display the number in parenthesis using ASP.Net and SQL Server 2000? Any clues will be greatly appreciated.
Thanks
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 RelatedI have an Excel/ vba module to update a SQL database. One of the data is numeric. If the data is a positive number, there is no problem, but if the number (zscore) in Excel is negative an error pops up: Error converting datatype varchar to numeric.
zscore = Range("J" & mytel99)
rc.Open "INSERT INTO dbo.QHSE_3rdline_history (RvA_Nr, RvA_Letter, Afdeling, Datum, Organisator, NummerRO, Eenheid, ALC, RO, Interpretatie) " _
& "VALUES ('" & rva1 & "', '" & rva2 & "', '" & Range("B" & mytel99) & "', '" & datum & "', '" & Range("B1") & "', '" & Range("B2") & "', '" & Range("G" & mytel99) & "', '" & Range("H" & mytel99) & "', '" & Range("I" & mytel99) & "', '" & Range("K" & mytel99) & "')", con
rc.Open "UPDATE dbo.QHSE_3rdline_history SET [Matrix] = '" & Range("C" & mytel99) & "', [Component] = '" & Range("D" & mytel99) & "', [Zscore] = '" &
[code]....
how to get negative data in a numeric SQL table?
I've got this issue with a query in SSIS. From a table in SQL Server I'm getting over 25000 different identifiers. These identifier are associated to many values in a table in one Oracle Database. This is the schema that I have implemented for doing this.
The problem is that some days the identifiers can be over 45000, and at this point perform a loop for every one is not the best solution (It can take to much time to get the result). Previously I have performed another query where from the SQL statement.
I am creating and sending a unique row with all the values concatenated and then I have recover this unique string from an object and use it to create the query in the ODBC Source that invoke the table in Oracle: something like this:
'Select * from Oracle_table' + @string_values
with @string_values = 'where value in (........)'. It works good because the number of values is small enough to be used, like 250. But in this case I can not use this approach because the number is really big and obviously the DBA of Oracle is going to cancel the query.
So I wonder, how can I iterate over the object getting only a few number of values everytime, something like 300 or maximum 500, to avoid the cancellation of the query but at the same time doing the minimum number of loops.
Hi there!
We are experiencing a problem using order clause in the statement below:
SELECT
person.pkey
, first_name
FROM
person
, private_person
WHERE
fkey_person = person.pkey
AND first_name = 'A'
ORDER BY
first_name
, person.pkey
Where person table contains the following columns:
pkey, name etc
and private_person
pkey, fkey_person (indexed), first_name (indexed)
The output is:
STEP 1
The type of query is SELECT
FROM TABLE
private_person
Nested iteration
Index : i$private_person$first_name
FROM TABLE
person
Nested iteration
Using Clustered Index
pkey first_name
----------- ---------------------
2000512 A
10994 A
2299 A
1097 A
1218 A
5133 A
1329 A
1387 A
1465 A
7532 A
5513 A
1884 A
512 A
591 A
(14 row(s) affected)
STEP 1
The type of query is SETOFF
Why SQL server does not order by pkey? Is there any information about it somewhere? Is it a bug or what?
If we are ordering by fkey_person everything is Ok. Can anybody help?
Thanks a lot!
Kind Regards,
Vladimir
I have a table named as C1_Messages with fields Id, Messages, Dates.
My Id filed looks like this:
1000
1001
1008
1009
1084
1093
1098 etc.
But here I need to make these Ids in a order, means it sould be in a consecutive order. Like
1000
1001
1002
1003
1004
1005 etc.
So is there anyway to do this method in SQL?
Regards
Shaji
Hello,
I have made sql table called costreallocation consists of two columsn:
RuleID varchar(10)
Type varchar(20)
I run the following three queries:
insert into costreallocation(RuleId,Type)values('aa','a')
insert into costreallocation(RuleId,Type)values('dd','d')
insert into costreallocation(RuleId,Type)values('cc','a')
then when i notice that these three records are not inserted as they are run:
In the table thn there are three records :
aa a
cc d
dd d
but i need to be filled in the table as they run as:
aa a
dd d
cc d
your help is highly appreciated
Best regards
I have an int field - that will hold number for an ordering system. It's a databound field to a textbox in a grid, I want the default to be empty, however not all fields are required so i may have 1-6 as bound values, and the rest should fall in line behind them - the only thing i can think of to do is default the values to 9999 so they come after 1,2,3,4,5,6 etc... I don't like seeing 9999 in my bound fields.
thanks for any suggestions you may have.
Jeff
Hello.
I want to order a resultset from a table by two columns in another table. Here's how it looks:
[Books]
ID
Title
[linkBookAuthor]
BookID (Books.ID)
AuthorID (Authors.ID)
[Authors]
ID
Firstname
Lastname
AuthorOrder
As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname.
Thanks for any help.
Hiya!
I've got a little stored procedure:
CREATE PROCEDURE rICDCode @param1 char(15) = 'Description'
AS
SELECT DiagCode, ICD9ID, Description
FROM ICDCodes
ORDER BY @param1
and SQL is returning error 1008 - "The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."
I want to be able to use the same stored procedure for several different functions which all need the same rowset sorted differently. Any way to do this?
Thanks,
Sarah
Hi guys.
I want to create a top 20 product list from a few thousand products. I want the rest of the products to be grouped into 'others'...
I also want the products to be ordered by the facts in the cube. Thus the product dimension would dynamically change depending on the Time dimension thats being selected.
is this plausible ?
Thanks
Tom
Is it possible without using CASE statements, to order a result set by one field (if the second field return value is null) or another (if the second field return value is not null)
This would be on datetime fields.
Let's say I have 5 records with 2 date fields
sched_dt and arriv_dt.
sched_dt will always have a value, arriv_dt may or may not have a value.
In a single result set I would like to have the records with an arriv_dt sorted by arriv_dt and the ones without an arriv_dt sorted by sched_dt.
*Hope that makes sense*
Thanks
I have a table that has 4 dates for each record (birthday, anniversary, policy_start & policy_end). What I want to do is cycle through each record and then do some calculations using the gaps between the dates. So it
might be Calc1 using (birthday->anniversary), Calc2(anniverary->policy_start), Calc3(policy_start->policy_end), etc. BUT the dates might be in different orders (ie the birthday could come first - or the anniverary could, etc).
In VBA in Excel, I can use the SMALL function within my calcs and this returns the minimum date, the next smallest, the next & then the biggest. But how can I do this in SQL. I guess I can push the 4 dates out to a temp table, order it and then suck them back in - but I have no idea how to do this for all the records in my primary table
I've also tried using various WHERE statements (ie do Calc1 using Birthday & Anniversary WHERE Birthday < Anniversary and ........ - but the code gets awfully long.
Is it possible to write my own SMALL function which uses a bubble sort on the 4 dates & then returns the smallest, next smallest, etc
Any thoughts ?
Hi all,
Ive got a table with a field called 'morder' which orders a menu based on the values in this field (1 for position 1, 2 for position 2 etc...) for example
1 - menu item 1
2 - menu item 2
3 - menu item 3
4 - menu item 4
If I want to add a record to this table and put it at number 2 in the list, i need to update the table to then read...
1 - menu item 1
2 - NEW menu item 2
3 - menu item 2
4 - menu item 3
5 - menu item 4
I want to use a mssql or php function to re-order this field... is it possible??
Hope you can help, and hope it makes sense...
Ash
Hi, I was wondering, I have several columns of data that are able to be sorted on - the user just clicks on the column name. However, we have 1 column called order status where we don't want the order to be alphabetical (and it is a text field - a couple current possible values are Processing...In Production...Waiting For Approval). This order is not good for logical sorting - we would want to be able to specify what value would get order. Does that make sense? So we would want to be able to say that Processing is first in the display, Waiting For Approval would be next, then In Production would be next, and so on. Is there any possible way of doing this in a SQL query? Or am I going to have to manually modify all the data adding numbers to the beginning of each data so that it will sort on the numbers? Thanks everyone.
View 4 Replies View Relatedi add new column using alter command but i always found it in the end of table but
i want to add it in particular position between the columns...........
how to do so .............:)
Hi,I'm streaming data monitoring histories of components into a database table with the following three columns: Hour (DateTime), Id (Int64), and Value (Int32). The Value entry is an aggregate of all values sent from component Id during, and 59 minutes and 59 seconds after, the time listed in the Hour column.I had rather not have to sort the queries after pulling them from the database by date. So I tried to index the DB by the Hour column. Any column will inevitably have duplicates, since the uniqueness depends on a combination of Hour and PortId. But Indexing the Hour column doesn't result in INSERTs being in order as I had expected. Instead, every entry is listed in order of insertion.So. . .how can I keep such a table ordered by date on the disk? I'm afraid this will become very inefficient if this isn't nipped in the bud right now.Thanks so much for your help!-Brandon
View 4 Replies View RelatedI have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so..
January 10
January 11
January 12
January 13
January 14
etc
January 19
January 2
January 20
January 21
January 22
here is my sample code
select [month] + ' ' + [day] as [Date] from mytable
where [month] = 'january'
and [year] = '2004'
Thanks, Jeff