HELP plz solve some Queries

HELP plz solve some Queries

• DotNetRules

  Just a comment on why results comes in the order it does...

  If you need a certain guaranteed ordering each and every time a certain query is executed, there is only one option: use ORDER BY.

  If you do not use ORDER BY, reults will be returned in the order the server finds the rows. This may or may not coincide with clustered index orderings etc, it really doesn't matter. The main thing to remember here is that this order may or may not change each time this query is executed, it all depends on how the data is retrieved each time.

  /Kenneth

  
  

• ibis1

  Answer for your Querry 2

  declare @title1 varchar(20)
  set @title1='A%'
  SELECT EmployeeID FROM Employees where FirstName like @title1
  go
  SELECT EmployeeID FROM Employees

  
  first select statement shows result as
  2
  9
   

  and second querry shows result as

  3
  4
  8
  1
  2
  6
  7
  5
  9

  
  u are amazed y they both have difference even though u have not specified any order.

  The reason is : The first querry performs a clustured index search operation

  where as the second querry performs a non-clustured index search operation and hence the result is sorted out By POSTALCODE  column , that means the result is shown as it is in the table. ( table is default sorted on POSTALCODE while creation )

  to help my answer i suggest u to execute this

  declare @title1 varchar(20)
  set @title1='A%'
  SELECT EmployeeID FROM Employees where FirstName like @title1
  go
  SELECT EmployeeID,Postalcode FROM Employees

  this will result u

  
  first select statement shows result as
  2
  9

  and second querry shows result as

  3 98033
  4 98052
  8 98105
  1 98122
  2 98401
  6 EC2 7JR
  7 RG1 9SP
  5 SW1 8JR
  9 WG2 7LT

  see both have same order, this is only for this table , u may not get such unwanted results on some other table. But for precautions and a good practive always use order.

  Answer for your Querry 3

  USE pubs
  SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE title_id IN (SELECT title_id FROM titles WHERE type = 'popular_comp'))
  

  
  

• dundask

  Hi KingKarter,
  
  "Is it effecient way to use @Somevariable in the where clause"
  
  Is it inefficient not to give all users of an ATM always the same amount of money Thats what variable and conditions are for. I don’t know if I got your question right !
  

  "Both queries are giving all the EmployeedID but they Differ in their order even though i have not used any order by clause"
  
  Queries always bring back unordered results unless you use an order clause, if you don’t specify any order and do a plain "gimme all rows" then the rows are fetched one by one, in your case I assume by the primary key on employee, which is, as I guess a clustered index, which is physically orderd. SO if you want to have orderd results, specify an order clause.
  
  How to write Query like this in SQL Server.(In oracle we can write this query)
  
  select * from (select a,b from Table1 )
  
  You have to specify the SubQuery name to make it valid: Select * from (SELECT a,b FROM SomeTable) SomeSubQueryName
  
  
  Now i want cumulative sum ( same we used in stats.). Here is the output
  
  You have to determine in some way whch value already has been summed up, so you have to use something like an id column (or the primary key column), just to show you how it could work:
  
  Select Amount, SubQuery.SumAmount
  FROM SomeTable ST1,
  (
  SELECT SUM(ST2.Amount) FROM SomeTable ST2 WHERE ST2.ID <= ST1.ID
  ) SubQuery
  Order by ID
  
  
  I want Top 3 salaried employess but records are not 2 be repeated.
  
  Select TOP 3 MIN(Name),Salary
  FROM SomeTable
  GROUP BY Salary
  ORDER BY Salary DESC
  
  
  HTH; Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---

  
  

• RKA

  It is a good way to use variables in a querry as you can control the flow and also you can set the values of such variables depending on the results of some stored procedures or some querries.

  The perform better when compared to application logic

   

  
  

• Dave Hewitt

  hi Jens ,

  thank for ur reply ..it is very help full...thank u once again.

  for followinng question i nedd help

  Query1:"Is it effecient way to use @Somevariable in the where clause" my question was does it relates to performance issues if i compare a variable in the where clause and not doin it in my application logic.

  Query4.Now i want cumulative sum ( same we used in stats.). I dont have any fields as ID in my table .It have only salary field.

  Please Elobrate on Query4.

  Thanx in advance

  
  
• HELP plz solve some Queries